Getting at string data in SQL

Question

I have a row entry with the following format:

Site=[number];this=that;foo=bar;

[number] above can be from 1...infinity. So I need to split out the [number] to use in another select statements where clause. Site=[number] is always at the beginning in the string and the data is always separated by a semi-colon.

Answer 1

SELECT SUBSTRING(col, 1, CHARINDEX(col,';'))

Answer 2

declare @t nvarchar(100) = 'Site=230;this=that;foo=bar;';
select convert(int, substring(@t,6, charindex(';',@t,0)-6))

Answer 3

Why are you storing data in the database in this format? Split it up into columns so that you can do meaningful queries.

Answer 4

You can play with the string this way:

declare @tx as nvarchar(100)
set @tx = 'Site=[number];this=that;foo=bar;'

print substring(
    @tx, 
    CHARINDEX('[', @tx)+1, 
    CHARINDEX(']',@tx)-CHARINDEX('[',@tx)-1)

Hope this helps.

Answer 5

I don't have MS Sql Server available to try this out, but have you tried something like

Select field convert(bigint, substring(field, 6)) as thenum from thetable where condition=something

where field is the name of the field containing site=[number];...

The theory goes that substring will strip off site= off the beginning, and convert will (hopefully) convert the number portion and ignore the rest of the text from the semicolon onwards.

It may or may not work. If not you may need to write an elaborate function instead.