Change row in SQL Server to reduce

Question

How to return everything in a string from an SQL query to a specific character?

My data is as follows:

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

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

Thus, the end result will be "HD TV HM45VM"

Answer 1

Use SUBSTRING , but you will need to use CHARINDEX or PATINDEX to get the location of the character you want to stop the substring 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, separate 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'))

. LEN DATALENGTH.

Answer 2

, LEFT

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

Answer 3

Ok, I’ll hit him too and see if I can simplify:

SELECT LEFT(myfield', CHARINDEX(' - ', myfield) - 1)

When wrapping -in spaces, the delimiter is less likely to work, and there is no need to trim the result.

Caution: this, like other answers, will result in an error if it myfielddoes not contain a separator -.

Ultimately, if your table always has a dotted delimiter, you should have two fields.