thegunner
thegunner

Reputation: 7163

select row based on what a substring in a column might contain

I'm looking to select the primary key of a row and I've only got a column that contains info (in a substring) that I need to select the row.

E.g. MyTable


ID | Label

------------
11 | 1593:#:#:RE: test
12 | 1239#:#:#some more random text
13 | 12415#:#:#some more random text about the weather
14 | 369#:#:#some more random text about the StackOverflow

The label column has always a delimiter of :#:#:

So really I guess, I'd need to be able to split this row by the delimiter, grab the first part of the label column (i.e. the number I'm looking) to get the id I wanted.

So, If I wanted row with ID of 14, then I'd be:

Select ID from MyTable 
where *something* = '369'

Any ideas on how to construct something ..or how best to go about this:)

I'm completely stumped and haven't been able to find how to do this.

Thanks,

Upvotes: 1

Views: 515

Answers (1)

JNevill
JNevill

Reputation: 50273

How about:

WHERE label LIKE '369#%'? 

No reason to get fancy.

Although.. if you are going to do this search often, then maybe pre-split that value out to another column as part of your ETL process and index it.

Upvotes: 4

Related Questions