T-Sql query to clean up varchar column

Question

I have a varchar column in one of my tables with data like:

1234abc
1234abcde456757
1234abc Supervisor
1234abc456 Administrator

I want to "clean it" by removing any letters and numbers immediately following them so for the above examples I want to have:

1234
1234
1234 Supervisor
1234 Administrator

In another word, I want to keep the initial number and the last word. I'm using the SUBSTRING and CHARINDEX but those functions remove everything till the end of the string and I don't know the length of the part I need to remove.

Any suggestions?

Thanks

Answer 1

You could search for the first non-digit and the first space in a subquery. That also works if the number of digits isn't exactly four:

declare @t table (col1 varchar(50))
insert into @t select '12abc'
union all select '1234abcde456757'
union all select '1234abc Supervisor'
union all select '1234abc456 Administrator'
union all select '123456abc456 Administrator'

select  case when FirstNonDigit = 0 then col1
             when FirstSpace = 0 then substring(col1, 1, FirstNonDigit-1)
             else substring(col1, 1, FirstNonDigit-1) + 
                  substring(col1, FirstSpace, len(col1) - FirstSpace + 1)
             end
from    (
        select  patindex('%[^0-9]%', col1) FirstNonDigit
        ,       patindex('% %', col1) FirstSpace
        ,       col1
        from    @t
        ) subqueryalias

-->

12
1234
1234 Supervisor
1234 Administrator
123456 Administrator