Click here to Skip to main content
13,898,580 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi ,

I need assistance here for updating 1st 4 digit of a column value

for E.g i have value 999999999 (9 digit) and i need to mask 1st 5 digits with 11111

output should look like "111119999"

However, in the same column i have value called "MIS" i dont want to touch those value

what query should i use in Oracle sql developer.

What I have tried:

I have tried various update query like
UPDATE table SET TAX_ID = replace(TAX_ID, '4', '1111');
Posted
Updated 22-Aug-18 21:00pm
Comments
Mohibur Rashid 22-Aug-18 22:38pm
   
What if your customer id is 111119999, what will be the result.
harshitawkk 23-Aug-18 11:58am
   
then the query will overwrite this value
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

UPDATE table
    SET TAX_ID = STUFF(TAX_ID,1,4,'1111')
WHERE   TAX_ID NOT LIKE '%MIS%';


Assuming the string 'MIS' can exist anywhere in the field.
BTW, your two first sentences are contradicting each other, do you want to update the first four or five characters?
   
v2
Comments
harshitawkk 23-Aug-18 11:52am
   
UPDATE table
SET TAX_ID = replace(TAX_ID, '4', '1111')
WHERE TAX_ID NOT LIKE '%MIS%';
this will add 1111 in existing value .


i need to mask 1st 4 or 5 digit from the value

output should be 111119999
Jörgen Andersson 24-Aug-18 1:27am
   
Updated solution.
But you still need to add logic for knowing when to replace four or five characters
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Use this

var res = "1111" + TAX_ID.Substring(4);
   
Comments
Nelek 23-Aug-18 6:58am
   
Text copied from non solution #2 (by the enquirer):

Will this add 1111 to existing number .

My aim is that if I have Custmoer number as 999999999 then I need to mask or hide 1st 5 digit and output should look like 111119999
harshitawkk 23-Aug-18 11:55am
   
how should i use this
var res = "1111" + TAX_ID.Substring(4);

can you please help me with complete query and how will this mask my 1st 4 digit of existin value
Er. Puneet Goel 27-Aug-18 1:02am
   
you see its already adding 4 digits to your TAX_ID.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190306.1 | Last Updated 24 Aug 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100