5

I'm trying to return a substring of the following, it's comma delimited [only one comma]

City-City-City, State-State-State

Sometimes it's only one city and state, sometimes it's more than one of either [or both]

Basically, I need to just return the state initials pass the comma. What's the best way to do this? I'm looking into the substring function, but that doesn't seem that smart. I found a split function but it looks like overkill and I don't like to use code I don't understand.

Ex:

Cincinnati-Middletown, OH-KY-IN
Cleveland-Elyria-Mentor, OH
Abilene, TX

Output:

OH-KY-IN
OH
TX

Thanks for the answers;I just figured it out thanks to Sonam's starting point. Here's what I got. Haven't looked into it but it seems to returning the right stuff.

select substring(CBSAName,charindex(',',CBSAName)+1, LEN(CBSAName)) FROM CBSAMasterList

1 Answers1

9
select substring('Abilene, TX',charindex(',','Abilene, TX')+2,2)
Sonam
  • 3,406
  • 1
  • 12
  • 24
  • 1
    Hey Sonam! Thanks for helping me out again. With charindex(',',','Abilene, TX') how do I... search and do the manipulation on my whole table of 5000+ stuff. At the moment, I'm only getting TX back. Can I put SELECT statements in charindex or substring? –  Aug 27 '13 at 14:35
  • What value are you exactly looking for? – Sonam Aug 27 '13 at 14:40
  • Anything after the comma basically. I'm doing a query on 6000 entries and only care about the state they're in, which I know is after any comma. –  Aug 27 '13 at 14:42
  • In that case we can go for some larger value for substring function so that it can pick everything after the comma. select substring('Cincinnati-Middletown, OH-KY-IN',charindex(',','Cincinnati-Middletown, OH-KY-IN')+2,300) – Sonam Aug 27 '13 at 14:46
  • Here I've passed 300 as a second parameter for substring function which means it will pick up to 300 characters after the ',' – Sonam Aug 27 '13 at 14:48