CODEWITHSUNDEEP
sqloracle-database
Saipraveen Bellary
Saipraveen Bellary

Reputation: 11

Need to extract a specific attribute value from a delimited string

I need to extract a specific value from the column. output of my table is:

select * from table where id='123456'

This table has around 30 columns, out of which I need to check for the word in specific column say "column 1". the data in the column 1 is: 

Test1=Hi;Test2=Hello;Test3=Good;Test4=Morning`

Question: I need a query that extracts only specific word from the column "Test2", i.e output should be the value of Test2 i.e. Hello (I need Hello should only print as output)

Upvotes: 1

Views: 552

Answers (3)

user672739
user672739

Reputation:

You could also use (since 11g) REGEXP_SUBSTR to return the match you need

WITH test AS (SELECT 'Test1=Hi;Test2=Hello;Test3=Good;Test4=Morning' AS column_1 FROM DUAL)
SELECT REGEXP_SUBSTR(column_1, 'Test2=(.+?);', 1, 1, NULL, 1) FROM test;

Upvotes: 0

Sandip Chavan
Sandip Chavan

Reputation: 39

Try this : This will search string only... for specific column i think need to case statement.

DECLARE @String AS VARCHAR(10) = 'Hello'

SELECT * FROM a WHERE @String IN ( test1, test2, test3, test4, test5 )

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

i have to get that test2 is a value so i changed my query use like operator 

select * from table where id='123456' and col1 like 'Test2=Hello%'

Upvotes: 1

Related Questions