CODEWITHSUNDEEP
mysqlsql
Hayi
Hayi

Reputation: 6236

Check if a number is in string delimited by seperator

I have a varchar field like this 1-2-55-9

How can make a query to check if a number is in this field ?

select * from x where TEST

What test to do if For example 55 -> yes return record , 5 -> no , 7-> no

Upvotes: 1

Views: 42

Answers (3)

bvdb
bvdb

Reputation: 24710

I would just use a like statement. The syntax is standard SQL and therefor also applicable on other dbms like Oracle or SQL Server. 

select * from x where (test like '%-55-%') or (test like '55-%') or (test like '%-55')

The like patterns use wildcards (i.e. %). The example above uses 3 conditions (in order of appearance):

  • contains -55-
  • or starts with 55-
  • or ends with -55

Alternatively you could use a regex to combine all 3 conditions:

SELECT * FROM x WHERE test regexp '(^|-)55($|-)'

Here it says that the string 55 should be preceded either by a - or it should be at the beginning. And it should be followed by a - or should be at the end.

Here is a short explanation of the regex syntax:

  • (a|b) matches a or b
  • ^ means the start of a value
  • $ is the end of a value

PS: You could also use rlike instead of regexp, they are identical.

See also the mysql documentation for pattern matching.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Use INSTR Function

select case when INSTR ('1-2-5-9', '5') > 0 then 'Yes' else 'No' End

Update :

Use FIND_IN_SET Function.

SELECT case when FIND_IN_SET('55',replace('1-2-55-9','-',','))>0 then 'Yes' else 'No' END

Upvotes: 3

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

If string contains the required number return yes else return no.

 select if(LOCATE('5','1-2-5-9')>0,'yes','no')

Tested and works!

Upvotes: 0

Related Questions