SQL Server Extracting substring before alphabet in SQL

inkz8wg9 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(135)

Say I have the following strings contained in column1:

1) 12345BC01
2) 67890DE05

How can I formulate my SELECT clause to extract only the values before any alphabet character? So my output would look like:

1) 12345
2) 67890

I found the following solution, but it seems to grab everything AFTER the alphabet characters:

SELECT STUFF(column1,1,ISNULL(NULLIF(PATINDEX('%[^0-9]%',column1),0)-1,0),'')

I wish I could detail what else I've tried but I don't know the first thing about regex unfortunately. Any help would be greatly appreciated

2条答案

You can try like this:

Select left(column1,patindex( '%[^0-9]%', column1+'A')-1)
 from @YourTable

It would be easier to just use the left function and isnull the result if there are only numbers

select IsNull(Left(Col1, NullIf(PatIndex('%[^0-9]%', col1), 0)-1), col1)
from t;