extract only characters from a string which has both numeric and alpha 2

[crystalempress]

How do I extract only characters from a string which has both numeric and alphanumeric characters using SQL 8.0?

ex. 'abc192k122r' to get 'abckr'

 

[donutman]

You will need a UDF to do that, but it probably should be done at the application level. The following function is a recursive function which means that it's going to perform even slower than a normal UDF.

[Blue]CREATE[/Blue] [Blue]FUNCTION[/Blue] dbo.AlphaOnly [Gray]
   ([/Gray]@String [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray])[/Gray]
   [Blue]RETURNS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray]
[Blue]AS[/Blue]
[Blue]BEGIN[/Blue]
   [Blue]DECLARE[/Blue] @IPos [Blue]int[/Blue]
   [Blue]SET[/Blue] @IPos[Gray]=[/Gray][Fuchsia]PatIndex[/Fuchsia][Gray]([/Gray][red]'%[^a-z]%'[/red][Gray],[/Gray]@String[Gray])[/Gray]
   [Blue]IF[/Blue] @IPos[Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
      [Blue]SET[/Blue] @String[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@String[Gray],[/Gray]@IPos[Gray]-[/Gray]1[Gray])[/Gray][Gray]+[/Gray]
          [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@String[Gray],[/Gray]@IPos[Gray]+[/Gray]1[Gray],[/Gray]8000[Gray])[/Gray]
      [Blue]SET[/Blue] @String[Gray]=[/Gray]dbo.AlphaOnly[Gray]([/Gray]@String[Gray])[/Gray]
   [Blue]END[/Blue] 
   [Blue]RETURN[/Blue] @String
[Blue]END[/Blue]

Use it as follows:

[Blue]DECLARE[/Blue] @S [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray]
[Blue]SET[/Blue] @S[Gray]=[/Gray][red]'12ab34cdFG56'[/red]
[Blue]SELECT[/Blue] dbo.alphaonly[Gray]([/Gray]@S[Gray])[/Gray]

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

SELECT replace(replace(replace(replace(replace(replace(replace(replace(replace(TheColumn,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')
WHERE TheColumn LIKE '%[0-9]%'

Hehe. Who knows... this might be pretty fast.

 

[crystalempress]

Thanks donutman & esquared, that is exactly what we were looking for!

 

[ESquared]

By the way, Karl, your solution might make good use of the Stuff function instead of substrings.