updating 1st 5 digit of a column value (1 Viewer)

harshitawk

New member
Local time
Today, 04:11
Joined
Aug 22, 2018
Messages
3
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

Banishment Pending
Local time
Today, 03:11
Joined
May 11, 2011
Messages
11,611
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

New member
Local time
Today, 04:11
Joined
Aug 22, 2018
Messages
3
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

AWF VIP
Local time
Today, 00:11
Joined
Mar 9, 2014
Messages
5,423
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

New member
Local time
Today, 04:11
Joined
Aug 22, 2018
Messages
3
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

AWF VIP
Local time
Today, 00:11
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 04:11
Joined
Aug 6, 2018
Messages
32
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.
 

Users who are viewing this thread

Top Bottom