How to search ID of object nvarchar value on SQL Server

I have a table tableOne:

tableOne

+------+---------+
| ID | Pages |
+------+---------+
| 1 | 1,2,3 |
| 2 | 2,3 |
| 3 | 2 |
+------+---------+


Now I want to get ID of tableOne where my Page exists.

tableOne

Example query:

select * from tableOne where Pages = 3


Output should be:

+------+---------+
| ID | Pages |
+------+---------+
| 1 | 1,2,3 |
| 2 | 2,3 |
+------+---------+


5 Answers
5

Simple logic

SELECT * FROM tableone
WHERE Pages LIKE '%,3,%'
OR Pages LIKE '3,%'
OR Pages LIKE '%,3'


If you are using SQL Server 2016 and above then use STRING_SPLIT: (Not tested)

SELECT Id, Pages
FROM tableOne
CROSS APPLY STRING_SPLIT(Pages, ',');
WHERE value = '3'


Precise Answer:

You have to create SPLIT function to split strings (If you are not using SQL Server 2016 or above)

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END


And to use it:

SELECT DISTINCT id
FROM tableone
CROSS APPLY dbo.splitstring(Pages)
WHERE name = '3'


If you're using MySQL, you can use FIND_IN_SET:

FIND_IN_SET

SELECT * FROM tableone WHERE FIND_IN_SET(3, Pages);


Output:

ID Pages
1 1,2,3
2 2,3


You should consider changing your stored data, so each page will be stored in one row separately.

If you can't, then you could use this query:

SELECT *
FROM tableOne
WHERE ',' + Pages + ',' LIKE '%,3,%';


if you are using postgres sql

SELECT * FROM (select id, unnest(string_to_array(pages,','))::integer AS ids from tableone) as a where ids = 3


SELECT * FROM YourTbl
WHERE Yourcolumn LIKE '%word1%'


In your case:

SELECT * FROM tableone
WHERE Pages LIKE '%3%'


Finds any values that have "3" in any position

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.