sql server - SQL: Return first non null value from the string -

problem : want retrieve first non null value string, substring separated , separator.

scenario :

string 1 - ,1002682657

string 2 - 1002682683,

string 3 - ,,1002682684

string 4 - ,,,

string 5 - 1002682664,1002682663

expected result

resultstring 1 - 1002682657

resultstring 2 - 1002682683

resultstring 3 - 1002682684

resultstring 4 - null value

resultstring 5 - 1002682664

so retrieve wrote function below script

create function [dbo].[return_first_nonnull_value_from_list] (     @list nvarchar(max) ) returns nvarchar begin -- declare return variable here declare @returnlistpart nvarchar(max) declare @start int declare @end int declare @length int declare @length_string int  set @start = 1 set @end = charindex(',',@list,@start)  set @length = (@end - @start) + 1 set @length_string= (@end-@start)+1  set @pos = 0 set @nextpos = 1  while @start>0 , @end>0 , @length_string>0     begin         if (substring(@list, @start, 1) <> '') , (substring(@list, @start,2) <>'')             begin                                    set @returnlistpart = substring(@list,@start,@length)                 set @length_string= len(@returnlistpart)                 if @length_string > 1                     begin                         set @length_string =0                     end             end          else             begin             -- replace string null value if null                 set @list = ltrim(rtrim(stuff(@list,@start,@length,'')))                 set @length_string = len(@list)             end                  end  return rtrim(ltrim(@returnlistpart))  end 

but function doesn't return expected result. please me out in this?

with 2 assumptions question pretty easily. looks numbers 10 characters long, , have numerics (no characters).

with in mind pattern match so:

select case when [value] '%[0-9]%' substring([value], patindex('%[0-9]%', [value]), 10)          else null        end [value]   [#test] 

we can discount rows without numeric characters straight away , return null, rest first numeric character , next 10 chars.

a full sample run in sql server be:

create table [#test] (   [value] nvarchar(1000) )  insert [#test] ( [value] ) values  ( n',1002682657') insert [#test] ( [value] ) values  ( n'1002682683,') insert [#test] ( [value] ) values  ( n',,1002682684') insert [#test] ( [value] ) values  ( n',,,') insert [#test] ( [value] ) values  ( n',1002682664,1002682663')  select case when [value] '%[0-9]%' substring([value], patindex('%[0-9]%', [value]), 10)          else null        end [value] [#test]  drop table [#test]