|
mohan123
Posting Yak Master
India
208 Posts |
 Posted - 12/16/2012 : 23:53:55
|
hello all,
i have table which have huge records in that one column have 25 characters in which i need to pick up only 9 numbers (i.e 1Z 853 4E0 31 2198 903 2) in that i need to remove spaces and alphabets and special characters at last it should look like (185340312) only 9 digits
i have written query but it is working in some cases but not for all records.
declare @yak varchar(20)
select @yak = SUBSTRING(REPLACE(Stuff (UPS, PatIndex('%[!.+A-Z@#$%^&*()]%', UPS), 1, ''),' ',''),0,11) from newfakelist
update newfakelist set NationalID = SUBSTRING(REPLACE(Stuff(UPS, PatIndex('%[!.+A-Z@#$%^&*()]%', UPS), 1, ''),' ',''),0,11) from newfakelist
in this i am updating NATIONALid COLUMN WITH RECORDS FROM UPS.
for one record it gave result like this
1Z 089 016 86 2821 064 6 = 1089016862 exactly what i need
for another record it gave result like
1Z 9W0 4E3 79 0235 938 4 = 19W04E3790
why it is not showing accurate result like first one??
P.V.P.MOhan |
|
how to exactly pick numbers restricting characters
