ansaurus

Question

Change string in SQL Server to abbreviate

Answer 1

+6 A:

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.

OMG Ponies 2010-03-11 21:26:13

You might need to trim spaces afterwards - look at using LTRIM or RTRIM if that's the case.

OMG Ponies 2010-03-11 21:34:03

Does the trick. Thanks a bunch. How would I do do the opposite? How could I strip everything to left of the dash?

jeff 2010-03-12 03:29:38

I recommend LEN(). DATALENGTH() will fail if the OP either now or later plans to use `nvarchar`. Also, if the field is `varchar(1000)`, you can simply provide 1000 as the third argument to SUBSTRING() and avoid the LEN() call.

richardtallent 2010-03-12 06:03:06

Getting incorrect syntax on the last code block posted...

jeff 2010-03-12 14:38:21

@jeff: Forgot the comma - try now.

OMG Ponies 2010-03-12 15:30:54

Answer 2

+2 A:

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)

astander 2010-03-11 21:32:07

Upvoted for using LEFT() rather than SUBSTRING() (a better fit IMHO for readability), but you should substract 2 rather than 1. CHARINDEX() is 1-based and you only searched for the dash, not the preceding space.

richardtallent 2010-03-11 21:44:07

True, but will the space be there, then rahter use a **RTRIM**

astander 2010-03-11 21:45:46

Answer 3

A:

Ok, I'll take a stab at it too and see if I can simplify:

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

By wrapping the - in spaces, the delimiter is less likely to be tripped up, and there's no need to trim the result.

Caveat: this, like the other responses, will result in an error if myfield does not include the - delimiter.

Ultimately, if there is always a dashed delimiter in your table, you should consider having two fields.

richardtallent 2010-03-11 21:42:58

Spaces might not be present.

astander 2010-03-11 21:46:33

astander brings up a good point - PATINDEX would be a better choice for that, because it allows psuedo-regexes

OMG Ponies 2010-03-11 21:50:49

@astander - where do you get that from the OP? Also, while PATINDEX() allows use of wildcards, it isn't capable of expressions like "an optional *space*, followed by a dash". CHARINDEX() will perform better given the input data provided. Searching for the space will avoid an edge-case bug that is entirely likely if the first portion of the string contains a dash (since many product codes in the wild may contain dashes, but not usually prefixed by a space). If the data are all delimited consistently, string parsing should exploit the more robust delimiter to avoid false positive splits.

richardtallent 2010-03-12 05:57:36

ansaurus

tags:

views:

answers:

Change string in SQL Server to abbreviate

related questions