SQLTeam.com | Weblogs | Forums

Trim off integers


#1

I have the following locations

Locations:
1D12
12E5
41A8
MEZ148
PW14
5H21
A1
7B7

I need to write a script that will remove all integers after the character ONLY IF the location starts with an integer.
My final result will be:

Locations:
1D
12E
41A
MEZ148
PW14
5H
A1
7B

Thanks for your help


#2

So only match the pattern:

Some digits, then some non-digits, then some digits and no non-digits after that?

WHERE Locations LIKE '[0-9]%[^0-9]%[0-9]'

You could then find the right-hand-most non-digit with PatIndex and Reverse

SELECT 	CASE WHEN Locations LIKE '[0-9]%[^0-9]%[0-9]'
		THEN LEFT(Locations, LEN(Locations)-(PatIndex('%[^0-9]%', 
							REVERSE(Locations)) - 1))
		ELSE NULL
		END AS [Result]

#3

Perfect. Thank you!!