Checking data type after a split operaton in sql server

byMR

February 2, 2022

This is my question. I’m receiving a String with values that can be numberic or alphanumeric separated by commas:

‘1,2,3,4’ or ‘1,2,b’

My goal is to return another string with this conditions:

Numerica values : Return the same string

Alphanumeric values : Return the values between single quotation and separated by comma ‘1’,’2′,’b’

So far this is the solution that I have:

declare @values nvarchar(max) = '1,2,a', @result nvarchar(max)

set @result = (
    select REPLACE(@values, ',', '')
            )

begin try    
    set @result = @result*1
    set @result = @values    
end try 
begin catch
    set @result = ( select ''''+ STRING_AGG( value, ''',''') + ''''
        from string_split(@values, ','))
end catch

select @result

Is it possible to reach the result without involving a try/catch block? (Request of the arquitect)

>Solution :

Just another option (assuming no decimals)

Declare @S varchar(50) = '1,2,3,4'
--Set @S ='1,2,b'

Select case when try_convert(bigint,replace(@S,',','')) is null
            then ''''+replace(@S,',',''',''')+''''
            else @S
            end

Results