Select first element of a comma-separated list-CodePudding

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

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: