CODEWITHSUNDEEP
sql-server
opperman.eric
opperman.eric

Reputation: 417

Select string before character

I have a field that may or may not be suffixed with an underscore and a number. I would like to select the part of the string before the _, but the _ is not always present

In another question I found this:

SUBSTRING([LineNumber], 0, CHARINDEX('_', [LineNumber]))

It works nice for MH31014329_1, which it then turns into MH31014329, but where there is no _1 it produces a blank

A case statement to test for ='' works but this record set is >100k lines, and I don't think it is efficient.

How can I do this in the most efficient way?

Upvotes: 2

Views: 197

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Add a "FAIL-SAFE" to the charindex() It will do no harm if one already exists.

SUBSTRING([LineNumber], 0, CHARINDEX('_', [LineNumber]+'_'))

Upvotes: 3

Related Questions