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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Replace letters in the beginning of a field

Author  Topic 

taunt
Posting Yak Master

128 Posts

Posted - 2014-08-13 : 12:28:32
Hello I'm trying to figure out how to replace letters in the begining of a column. My data looks like this afqt45192, zx425569, a8915249 and I need it to be like this 45192, 425569, 8915249. I've been trying to use REPLACE(LTRIM, but that isn't working the best for me. Is there a better way to get a field to take out the beginning letters in a column? the database is items and the column name is catalogNumber. Any help would be great. So what I need is it to look for a-z in the first 6 charaters and replace it with '' (nothing).


Thanks

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-13 : 12:39:27
DECLARE @NumericString varchar(50) = 'zx425569'
SELECT SUBSTRING(@NumericString,PATINDEX('%[0-9]%',@NumericString),LEN(@NumericString))
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-08-13 : 12:47:08
Look at STUFF

djj
Go to Top of Page

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-13 : 13:42:55
If you use stuff you will still need to know how many characters to replace, so Stuff or substring , you will still probably need the PATINDEX to find where the first number starts if you do not consistently have the same number of characters to replace. If it is always the first 6 and you know they will always be present, stuff is the way to go.
Go to Top of Page
   

- Advertisement -