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.

Is this string manipulation logic going in a select clause or a where clause? If it's going in a where clause, I would consider refactoring your database schema because you're unlikely to get good performance out of it. — Asaph
– Asaph, Commented Nov 5, 2009 at 23:07

Nestor · Accepted Answer · 2009-11-05 23:08:55Z

2

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

answered Nov 5, 2009 at 23:08

Nestor

14k11 gold badges83 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris McCall · Accepted Answer · 2009-11-05 23:07:16Z

1

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

answered Nov 5, 2009 at 23:07

Chris McCall

10.4k9 gold badges51 silver badges84 bronze badges

Comments

jmucchiello · Accepted Answer · 2009-11-05 23:10:44Z

1

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

answered Nov 5, 2009 at 23:10

jmucchiello

19k7 gold badges45 silver badges62 bronze badges

3 Comments

BalusC Over a year ago

+1: this particular problem ought to be solved at a higher level: the datamodel.

user204265 Over a year ago

That is the way the vendor did it. I told them it sucks but I have to deal with it now.

jmucchiello Over a year ago

The original questioner didn't think I needed to be dinged but 3 months later, you did. Thank you for keeping stackoverflow safe, Chris McCall.

j.a.estevan · Accepted Answer · 2009-11-05 23:12:22Z

0

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.

answered Nov 5, 2009 at 23:12

j.a.estevan

3,10720 silver badges34 bronze badges

Comments

hookenz · Accepted Answer · 2009-11-05 23:13:38Z

0

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.

answered Nov 5, 2009 at 23:13

hookenz

39.6k50 gold badges197 silver badges308 bronze badges

Collectives™ on Stack Overflow

Getting at string data in SQL

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related