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 |
|
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)) |
 |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-08-13 : 12:47:08
|
| Look at STUFFdjj |
|
|
|
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. |
|
|
|
|
|
|
Replace letters in the beginning of a field