CODEWITHSUNDEEP
sql-server
Sonam Mohite
Sonam Mohite

Reputation: 903

how to check particular ID is present in string

I want to check if a ID is present or not in a set of multiple ID. Example

if id 5 is present in multiple id list like '1,2,3,4,5'

Upvotes: 0

Views: 2117

Answers (6)

Sonam Mohite
Sonam Mohite

Reputation: 903

I have tried something like this

DECLARE @item VARCHAR(50)
SET @ExpressionToSearch = '23,12'

SET @item = '23'
SELECT COUNT(*) from dbo.Split(@ExpressionToSearch,',')
  WHERE ',' + (@ExpressionToSearch) + ',' 
    LIKE '%'+@item +'%'

This scenario will return output greater than 0

Upvotes: 0

Vivek Jain
Vivek Jain

Reputation: 3889

One good solution in this context could be to store the values with leading and trailing comma, as:

',1,2,3,4,5,'

So, even if you store one value, store it as:

',5,'

This will help you look for required value as:

SELECT column FROM table WHERE column like '%,5,%';

Or

SELECT column FROM table WHERE CHARINDEX(',5,',(',' + column + ',')) > 0

Also, using this solution you can uniquely identify different values, given that the string does not contain duplicate values.

So, it is easy to identify 5, 50 and 350 from:

',5,50,150,250,350,450,500,'

I am not sure if you can change how the values can be stored. Please read this answer in light of the context of the question - as a starting point and not as a copy-paste solution.

Upvotes: 0

Mihai
Mihai

Reputation: 26784

SELECT column FROM table WHERE CHARINDEX(',5,',(',' + column + ',')) > 0

Fiddle

Upvotes: 0

user3436543
user3436543

Reputation: 61

If you are talking about sql this query will work for you, if you get nothing it means searching id is not there in the list else there.

SELECT * FROM journal_pulses WHERE mood_credentials in (2,27)

Upvotes: 0

Oasis
Oasis

Reputation: 478

use CHARINDEX to return the index of the searched value.

Declare @MyString varchar(50)
Set @MyString  = '1,2,3,4,5'
IF CHARINDEX('5',ContactName) > 0 
 Begin
    Print '5 Exists'
 End
Else
 Begin
    Print  '5 Does not exists'
 End

Upvotes: 1

Sky
Sky

Reputation: 3360

select count(*) FROM table where id = '5';

If it returns more than 0 means it's present.

Upvotes: 0

Related Questions