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 |
|
webaholik
Starting Member
2 Posts |
 Posted - 2011-04-14 : 12:35:50
|
| I have a column with several thousand part numbers that show up with 3 letters at the beginning I need removed.example:ZEX82238ZEX82177BLY41500SPC141-1321120Would like to see:822388217741500141-1321120length varies, but basically if it starts with 3 letters, I want those letters removed. What sql command would I used to accomplish this? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-14 : 12:52:05
|
| [code]UPDATE yourTable SET yourColumn = CASE WHEN yourColumn LIKE '[A-Za-z][A-Za-z][A-Za-z]%' THEN STUFF(yourColumn ,1,3,'') ELSE yourColumn END;[/code] |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-14 : 12:52:48
|
| update tblset col = stuff(col,1,3,'')where col like '[A-Z][A-Z][A-Z]%'==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-14 : 13:01:18
|
I would pick Nigel's over mine  |
|
|
|
webaholik
Starting Member
2 Posts |
Posted - 2011-04-14 : 13:06:54
|
quote:
Originally posted by sunitabeck
I would pick Nigel's over mine
Both result with:#1305 - FUNCTION MYDATABASE.stuff does not exist we have mysql 5.0.77 installed |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-14 : 13:12:52
|
| STUFF is a Microsoft T-SQL function. This forum is a Microsoft SQL forum, so there may be few if any here who are familiar with MySQL. You might ask in a MySQL forum or general forums such as dbforums.com.You could try to replace stuff(col,1,3,'') with right(col,len(col)-3)) and see if that works. If not, I am out of ideas. Even if that seems to work, test to see if it works correctly when there are trailing spaces. |
|
|
|
|
|
|
|
|
Remove first 3 letters in SQL column