Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mohan123
Constraint Violating Yak Guru
252 Posts |
 Posted - 2012-12-16 : 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 newfakelistupdate newfakelist set NationalID = SUBSTRING(REPLACE(Stuff(UPS, PatIndex('%[!.+A-Z@#$%^&*()]%', UPS), 1, ''),' ',''),0,11) from newfakelistin 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 = 19W04E3790why it is not showing accurate result like first one??P.V.P.MOhan |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
how to exactly pick numbers restricting characters