sql server - Keep only desired characters and separate with semicolon in T-SQL -

the problem:

i have text data imported db lot of unwanted characters. need keep 4 capital letter strings within imported text string. example:

1447;#mibd (this nice name);#2056;#lkre (very nice name indeed) 

this in 1 column in 1 row of table. need extract string is:

mibd , lkre 

and result should preferably desired strings separated semicolons.

it should applied whole column , cannot know how many of these 4 upper case letter strings might appear in 1 row.

went through sorts of function patindex etc. not know how approach it. help!

try this, assumes 4 char code preceded ;# . patindex case insensitive have added additional check verify 4 character capital.

declare @mytable table( id int, mystring varchar(8000))  insert @mytable values      (1, '1447;#mibd (this nice name);#2056;#lkre (very nice name indeed)')     ,(2, ';#dbcc (this nice name);#2056;#llc (very nice name indeed) ;#abcd')     ,(3, ';#aaaa;#opqr;1234 (and) ;#wxyz')     ,(4, ';#abc empty string without code')  ;with cte  (     select id          ,substring(mystring, patindex('%;#[a-z][a-z][a-z][a-z]%',mystring)+2, 4) newstring         ,stuff(mystring, 1, patindex('%;#[a-z][a-z][a-z][a-z]%',mystring)+6, '') mystring     @mytable m     patindex('%;#[a-z][a-z][a-z][a-z]%',mystring) > 0      union      select id          ,substring(mystring, patindex('%;#[a-z][a-z][a-z][a-z]%',mystring)+2, 4) newstring         ,stuff(mystring, 1, patindex('%;#[a-z][a-z][a-z][a-z]%',mystring)+6, '') mystring     cte c     patindex('%;#[a-z][a-z][a-z][a-z]%',mystring) > 0      )   select c.id,     stuff(( select '; ' + newstring             cte c1             c1.id = c.id                 , ascii(substring(newstring, 1, 1)) between ascii('a') , ascii('z')  -- first char                 , ascii(substring(newstring, 2, 1)) between ascii('a') , ascii('z')  -- second char                  , ascii(substring(newstring, 3, 1)) between ascii('a') , ascii('z')  -- third char                  , ascii(substring(newstring, 4, 1)) between ascii('a') , ascii('z')  -- fourth char              xml path(''), type).value('.', 'varchar(max)')      -- use value clause hanlde xml character issue like, &,",>,<         ,1,1,'') codelist cte c group id option (maxrecursion 0);