Extract string from one character to another in SQL and remove leading and trail spaces

Hi I have the following strings in a SQL table Cities:

       Info
AUS / Melbourne (AUS) 16th Jul
AUS / Sydney (AUS) 16th Jul
USA/ New York (USA) 16th Jul

I am hoping to extract and create a new column so my desired result is:

      Info                           City
AUS / Melbourne (AUS) 16th Jul      Melbourne (AUS)
AUS / Sydney (AUS) 16th Jul         Sydney (AUS)
USA / New York (USA) 16th Jul        New York (AUS)

I have tried something like:

Select 
[Info],
SUBSTRING([Info],CHARINDEX('/ ',[Info])+2,10) as City
from Cities

But thats a fixed width of 10 characters, rather I would like the string from the / to the closed brackets )

Any help would be much appreciated!

Thanks very much

Answer

Rather than use a fixed length why not just use charindex like you have done but for both delimiters?

select Substring([info], CharIndex('/',[info])+2, IsNull(NullIf(CharIndex(')',[info]),0)-CharIndex('/',[info])-1,Len([info])))

Advertisement

Answer