1

I have values stored like this in a field 1,255,230,265.

Is there a function in MySQL that will give me the second value in that string? In this case it'll be 255.

I tried using locate, but that does not seem to be meant for this.

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
Norman
  • 6,159
  • 23
  • 88
  • 141
  • Do you want second value or a specific value no matter at what location? That makes a huge difference in approach you should follow – Bhavik Shah Jan 30 '14 at 06:14
  • @BhavikShah I want only the second value in any given set. In this case its 255 – Norman Jan 30 '14 at 06:16
  • This link might be useful: https://www.giannistsakiris.com/2015/01/23/mysql-select-the-n-th-element-from-a-delimiter-separated-value-set/ – shasi kanth Aug 29 '19 at 08:32

4 Answers4

5

Try this

select  SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,',',2),",",-1)  from table_name
naveen goyal
  • 4,571
  • 2
  • 16
  • 26
  • The only caveat is that `field_name` should have at least 2 values. If it has only one value, then that value is returned. For eg: `select substring_index( substring_index( 'a', ',', 2), ',', -1)` returns `a` – arun Mar 06 '14 at 21:45
2

You might want to use SUBSTRING_INDEX() function.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(field,',',2),',',-1)
FROM yourTable.

This grabs everything infront of the second comma, then grabs everything after the last comma (-1)

Tin Tran
  • 6,194
  • 3
  • 19
  • 34
0

Try This

select * from Table1 where ',' + Nos+ ',' like '%,255,%'

Refer:

MySQL query finding values in a comma separated string

mysql check if numbers are in a comma separated list

Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

Use FIND_IN_SET() function: 

SELECT * 
FROM tableA
WHERE FIND_IN_SET(255, columnName)

OR

Use LIKE operator

SELECT * 
FROM tableA
WHERE CONCAT(',', columnName, ',') LIKE '%,255,%'
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83