updating 1st 5 digit of a column value

[harshitawk]

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.

 

[plog]

Why is this column serving double duty? Further, why are you mixing text and numbers in it? I think your issue is bigger than this one symtpom you've described.

Further, why must this field be updated? Perhaps a better solution is creating a query to display the correct value you want. Can you better describe what this is data is and what your ultimate aim is?

 

[harshitawk]

why is this column serving double duty? BecauE for some customer I don’t have number so for missing value m using MIS

Further, why are you mixing text and numbers in it? For missing value we are using MIS .


Further, what your ultimate aim?
If I have 999999999 as customer number then all I need is to mask 1st 5 digit so that final value should be 111119999


I hope I answered all your question

 

[June7]

So customer numbers are identifying data not really numbers as a quantifying value would be. It's perfectly reasonable for this to be a text field. Do you want to actually change the saved data? Or just modify how displayed?

 

[harshitawk]

I'm not very sure what is the yor question

all i need is to mask the 1st 4 digit and output should be 11119999 instead of 99999999

 

[June7]

Suggest using MISS for the missing prefix. Not sure what you are asking for. If you want to substite first 4 digits with number 1 in output on report, then use an IIf() expression, like:

IIf(x Like "M*", x, "1111" & Mid(x, 5))

 

[theseus]

Assuming by the area this was posted in, you are using SQL Server. SQL Server has a Mask function that can be used to dynamically mask results as they are pulled. You would need to do a custom mask.


You can find more information here.