split string by character

DECLARE @string VARCHAR(100) = 'A8A61'

DECLARE @pos int

DECLARE @t table(val varchar(10))

 

WHILE CHARINDEX('A', @string) > 0

begin

            select @pos = CHARINDEX('A', REVERSE(@string    ((

            insert into @t select 'A'+reverse(substring(REVERSE(@string),1,@pos-1((

            select @string = SUBSTRING(@string,1,len(@string)-@pos)

end     

select distinct top 1

               case

                                    when a.val = b.val then ISNULL(b.val,'A')

                                    else 'A'

               END as new_val,      

               case 

                                    when a.val = b.val then ISNULL(CHARINDEX(b.val,'A8A61'),0)

                                    else 0

               end as flag

  from @t a inner join table_name b

    on (a.val= b.val

    or not exists (Select 1 from table_name where val= a.val))

order by 2 desc