CODEWITHSUNDEEP
sqlsql-servertrim
James Khan
James Khan

Reputation: 841

Trimmining a column with bad data

My data looks like

ID    LPNumber 
1     30;#TEST123
2     302;#TEST1232

How can I update MyText to drop everything before the # and including the #, so I'm left with the following:

ID    LPNumber 
1     TEST123
2     TEST1232

I've looked at SQL Server Replace, but can't think of a viable way of checking for the ";"

Upvotes: 2

Views: 167

Answers (2)

gbn
gbn

Reputation: 432421

On the MSDN REPLACE page, the menu on the left gives the complete list of string functions available.

UPDATE
   MyTable
SET
   LPNumber = SUBSTRING(LPNumber, CHARINDEX('#', LPNumber)+1, 8000);

I'll let you work out (from MSDN) the filter needed in case there is no # in the column...

Edit:

Why 8000?

The longest non-LOB string length is 8000 so it is shorthand for "until end of string". You can use 2147483647 too for max columns or to make it consistent.

Also, LEN can bollix you.

You'd need to use DATALENGTH but then you need to know the data type because this counts bytes, not characters. See https://stackoverflow.com/a/2557843/27535 for example

So using a magic number is perhaps a lesser evil...

Upvotes: 6

MatBailie
MatBailie

Reputation: 86765

Use CHARINDEX(), LEN() and RIGHT() instead.

RIGHT(LPNumber, LEN(LPNumber) - CHARINDEX('#', LPNumber, 0))

Upvotes: 3

Related Questions