Split a number in a string-CodePudding

I have a column called "Site" with value as following:

Clj2
Cob
Cob2
Abt 234
Abt4
Bani

I would like to take only the alpha chars into a new column "Sitename" without numbers or spaces which means the result should be like this:

Clj
Cob
Cob
Abt
Abt
Bani

Below is the code that I checked...

Code 1:

WHEN TableUser.[site] LIKE '%0%' 
THEN Substring(TableUser.[site],0, CHARINDEX('%0%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%1%' THEN Substring(TableUser.[site],0, CHARINDEX('%1%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%2%' THEN Substring(TableUser.[site],0, CHARINDEX('%2%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%3%' THEN Substring(TableUser.[site],0, CHARINDEX('%3%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%4%' THEN Substring(TableUser.[site],0, CHARINDEX('%4%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%5%' THEN Substring(TableUser.[site],0, CHARINDEX('%5%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%6%' THEN Substring(TableUser.[site],0, CHARINDEX('%6%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%7%' THEN Substring(TableUser.[site],0, CHARINDEX('%7%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%8%' THEN Substring(TableUser.[site],0, CHARINDEX('%8%',TableUser.[site],0))
WHEN TableUser.[site] LIKE '%9%' THEN Substring(TableUser.[site],0, CHARINDEX('%9%',TableUser.[site],0))
WHEN CHARINDEX(' ',(TableUser.[site])) >0 THEN Substring(TableUser.[site],0, CHARINDEX(' ',TableUser.[site],0))
ELSE TableUser.[site]
END as [sitename]

Code 2:

CASE
WHEN CHARINDEX('[0-9]',(TableUser.[site])) >0 THEN Substring(TableUser.[site],0, CHARINDEX('[0-9]',TableUser.[site],0))
WHEN CHARINDEX(' ',(TableUser.[site])) >0 THEN Substring(TableUser.[site],0, CHARINDEX(' ',TableUser.[site],0))
ELSE TableUser.[site]
END as [sitename]

**'[0-9]' also tried with %[0-9]% or %[^0-9]% or [[:digit:]]

It works with the ' ' part (Abt 234 --> Abt) this OK, but not with the number (Cob2 is still shown as Cob2)

Can anyone help me out? Thank you so much for your support!

CodePudding user response：

You can try with a regex that will match everything except the first characters like this:

SELECT CASE WHEN PATINDEX('%[^A-Za-z]%', site) = 0 
            THEN site
            ELSE SUBSTRING(site, 1, PATINDEX('%[^A-Za-z]%', site)-1)
       END
FROM TableUser

If there's no other character than char in the string, it'll return the string as is, otherwise it truncates the string at the first instance of different char.

Pattern %[^A-Za-z]%: matches the first occurrence of any non-alphabetical character.

Try it here.

CodePudding user response：

you can use translate()

Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.

trim(translate(site, '0123456789', '          '))

trim() is to remove any space at the start or end of the string after translate