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)

CodePudding user response：

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

'1','2','b'

or

1,2,3,4

Note: If the value > 9,223,372,036,854,775,807 then you could use try_convert(float,...)

CodePudding user response：

You could amalgamate your logic into a single statement as follows

select 
  case when v like '%[A-z]%' then alpha
    else v
  end
from (select @values)v(v)
cross apply (
    select ''''  STRING_AGG( value, ''',''')   ''''
  from string_split(v, ',')
)s(alpha)

Example Fiddle

CodePudding user response：

fiddle

declare @values nvarchar(max) = replicate('1,2.5,3', 15);
select case isjson('[' @values ']') when 1 then @values else '''' replace(@values, ',', ''',''') '''' end;