Change string in SQL Server to abbreviate

Question

How do I return the everything in a string from a sql query before a certain character?

My data looks like this:

HD TV HM45VM - HDTV widescreen television set with 45" lcd

I want to limit or truncate the string to include everything before the dash.

So the final result would be "HD TV HM45VM"

Answer 1

Use SUBSTRING, but you'll have to use either CHARINDEX or PATINDEX to get the location of the character you want the substring to stop at:

SELECT SUBSTRING('HD TV HM45VM - HDTV widescreen television set with 45" lcd', 
                  0, 
                  CHARINDEX('-', 'HD TV HM45VM - HDTV widescreen television set with 45" lcd'))

To do the opposite - strip everything to the left of the hyphen - use:

SELECT SUBSTRING('HD TV HM45VM - HDTV widescreen television set with 45" lcd', 
                  CHARINDEX('-', 'HD TV HM45VM - HDTV widescreen television set with 45" lcd') + 1,
                  LEN('HD TV HM45VM - HDTV widescreen television set with 45" lcd'))

Effectively, make the location of the hyphen to be the starting point. Then you can use either LEN or DATALENGTH functions.

Answer 2

Dont forget that LEFT exists

select LEFT('HD TV HM45VM - HDTV widescreen television set with 45" lcd',CHARINDEX('-', 'HD TV HM45VM - HDTV widescreen television set with 45" lcd ')-1)