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');
Updated 22-Aug-18 21:00pm
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
Please Sign up or sign in to vote.

Solution 3

UPDATE table
    SET TAX_ID = STUFF(TAX_ID,1,4,'1111')

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?
harshitawkk 23-Aug-18 11:52am
UPDATE table
SET TAX_ID = replace(TAX_ID, '4', '1111')
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
Please Sign up or sign in to vote.

Solution 1

Use this

var res = "1111" + TAX_ID.Substring(4);
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