Home > database >  Select first element of a comma-separated list
Select first element of a comma-separated list

Time:11-23

I have a field column that contains values like

4  
12,3  
8,5,6,7  

I'm going to write a SELECT statement, whose result would be:

4  
12  
8  

How can I do it?

CodePudding user response:

Try this

I'm hoping you are looking for this kind of solution.

DECLARE @table TABLE
(
    ID INT IDENTITY(1,1),
    Value NVARCHAR(MAX)
)

INSERT INTO @table VALUES('4'),('12,3'),('8,5,6,7')

select *,
IIF(CHARINDEX(',', [Value]) = 0, [Value],SUBSTRING([Value],1,(CHARINDEX(',',[Value])-1))) AS [OutPut]
from @table

Output

enter image description here

CodePudding user response:

Try this

For SQL Server:

select if(charindex(',', numbers) = 0, numbers, LEFT(numbers, charindex(',', numbers) - 1))
from table;

For MySQL:

select if(instr(numbers, ',') = 0, numbers, LEFT(numbers, instr(numbers, ',') - 1))
from table;

CodePudding user response:

Try this for SQL server

DECLARE @CSV_Split TABLE
(
    Id INT IDENTITY(1,1),
    CSVData NVARCHAR(MAX)
)

INSERT INTO @CSV_Split VALUES('4'),('12,3'),('8,5,6,7')

select *,LEFT(CSVData, CHARINDEX(',', CSVData   ',') - 1)
from @CSV_Split

The output is something like this:

enter image description here

  • Related