need help in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

need help in query

Author

Topic

rjaiswal
Starting Member

1 Post

Posted - 2015-04-10 : 09:01:30

I have data as

id value
1 a,b,c
2 a1,b1,c1
3 1,2,3
4 11,21,31

I need query to return single row upon specifying any specific substring considering , is delimiter .i.e.

Query can take 'a' as input and return only 1st row ...or a1 as input and return 2nd row .

Thanks for helping

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 09:28:50

CREATE TABLE #t
(
ID int identity(1,1),
Value varchar(50)
)

INSERT INTO #t
VALUES('a,b,c'),(' a1,b1,c1'),('1,2,3'),('11,21,31')

DECLARE @Find char(1) = '1'

SELECT TOP 1 * FROM #T
WHERE PATINDEX('%' + @Find + '%',value ) > 0
Or if you don't want to use top.

SELECT * FROM #T
WHERE ID = (SELECT MIN(ID) FROM #T WHERE PATINDEX('%' + @Find + '%',value ) > 0 )

some variation on this should work for you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources