CODEWITHSUNDEEP
sqlsas
Wolff
Wolff

Reputation: 1091

How do you query a table filtering on a substring of one of the columns?

I have a table I wish to query. It has a string variable called comment which contains an ID along with other things. (i.e. "123456;varA;varB")

rowNo comment
1 "123456;varA;varB"
2 "987654;varA;varB"

I want to filter based on the first substring in the comment variable.

That is, I want to filter the table on rows where the first substring of comment is "123456" (which in the example would return the first row)

How do I do this?

I was thinking something along the lines of the code below, using the "string_split" function, but it doesn't work.

SELECT *,
FROM table
WHERE (SELECT value FROM STRING_SPLIT(comment,';',1)="123456")

Does anyone have any ideas?

Note, I am querying in SQL in SAS, and this is on a large dataset, so I don't want to create a new table with a new column to then query on instead. Ideally I'd want to query on the existing table directly.

Upvotes: 0

Views: 619

Answers (1)

Tom
Tom

Reputation: 51566

You can use the SCAN() function to parse a string.

WHERE '123456'=scan(comment,1,';')

Upvotes: 2

Related Questions