CODEWITHSUNDEEP
sqlsql-server
alwaysaskingquestions
alwaysaskingquestions

Reputation: 1657

charindex working on some string but not another. why the inconsistency?

so I have a table with strings of different time zone. Some of the time zones have parenthesis after them such as:

  1. Central Daylight Time (North America)
  2. Mountain Standard Time (Mexico)

So we want to get rid of everything inside the parenthesis, and I am using charindex to do so:

select TimeZoneName,
CHARINDEX( ' (' , TimeZoneName) as indexTZ2,
Case When CHARINDEX( ' (' , TimeZoneName)= 0 Then TimeZoneName 
    Else LEFT(TimeZoneName,CHARINDEX ( ' (' ,TimeZoneName)-1 ) End
as TZ2
from TimeZone

and here's my output:

TimeZoneName                            indexTZ2    TZ2
Mountain Standard Time (North America)  0           Mountain Standard Time (North America)
Mountain Standard Time (Mexico)         23          Mountain Standard Time

Now I am very confused, why the same code works on one (Mexico one) of them and not the other (North America one)? the data is from the same table, so the datatype in this column is the same.

Upvotes: 0

Views: 252

Answers (2)

John Pasquet
John Pasquet

Reputation: 1842

I'm guessing the issue is with the whitespace character that precedes the parenthesis. That's the only thing that makes sense. Perhaps it is a tab character or a non-breaking space.

My suggestion is to exclude that charter and then subtract 2.

SELECT TimeZoneName,
    CHARINDEX( '(' , TimeZoneName) as indexTZ2,
    Case When CHARINDEX( '(' , TimeZoneName)= 0 Then TimeZoneName 
    Else LEFT(TimeZoneName,CHARINDEX ('(' ,TimeZoneName) - 2) End as TZ2
FROM TimeZone

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269603

A simple way to implement this logic is to remove everything after the first (. So:

select left(TimeZoneName, charindex(' (', TimeZoneName + ' ('))

Note that this appends the ' (' to ensure the pattern is found somewhere, if only at the end of the string being searched.

The reason way your code (and probably the above code) won't work is probably because there is some other character in the data. I might recommend just focusing on the ( rather than on the space before it:

select left(TimeZoneName, charindex('(', TimeZoneName + '(') - 1)

Upvotes: 0

Related Questions