How to remove any trailing numbers from a string?

Question

Input Examples:

"Hi how are you"

"What is the No. 1 pizza in New York?"

"Domino - number 1"

"Blah blah-123123"

"More blah 12321 123123 123132"

Expected Result:

"Hi how are you"

"What is the No. 1 pizza in New York?"

"Domino is the number"

Blah blah

"More blah"

I think this is a two-step process:

• Divide the entire line into characters, one line per character (including spaces), in reverse
• Scroll, and for each, if it is a space or a number, skip, otherwise add the beginning of another array.

And I have to get the desired result.

I can come up with some quick and dirty ways, but it needs to be done quite well, since it is a trigger that works on a busy table, so I thought I would send it to T-SQL specialists.

Any suggestions?

Answer 1

This solution should be a little more efficient, as it first checks to see if the string contains a number, and then checks to see if the string ends with a number.

CREATE FUNCTION dbo.trim_ending_numbers(@columnvalue AS VARCHAR(100)) RETURNS VARCHAR(100) BEGIN --This will make the query more efficient by first checking to see if it contains any numbers at all IF @columnvalue NOT LIKE '%[0-9]%' RETURN @columnvalue DECLARE @counter INT SET @counter = LEN(@columnvalue) IF ISNUMERIC(SUBSTRING(@columnvalue,@counter,1)) = 0 RETURN @columnvalue WHILE ISNUMERIC(SUBSTRING(@columnvalue,@counter,1)) = 1 OR SUBSTRING(@columnvalue,@counter,1) = ' ' BEGIN SET @counter = @counter -1 IF @counter < 0 BREAK END SET @columnvalue = SUBSTRING(@columnvalue,0,@counter+1) RETURN @columnvalue END 

If you run

 SELECT dbo.trim_ending_numbers('More blah 12321 123123 123132') 

He will return

 'More blah' 

Answer 2

A cycle on a busy table will be very unlikely to be completed. Use REVERSE and PATINDEX to find the first digit, start OPEN there, and then UNLOCK the result. It will be rather slow, without cycles.

Your examples assume that you also do not want to match spaces.

 DECLARE @t TABLE (s NVARCHAR(500)) INSERT INTO @t (s) VALUES ('Hi there how are you'),('What is the #1 pizza place in NYC?'),('Dominoes is number 1'),('Blah blah 123123'),('More blah 12321 123123 123132') select s , reverse(s) as beginning , patindex('%[^0-9 ]%',reverse(s)) as progress , substring(reverse(s),patindex('%[^0-9 ]%',reverse(s)), 1+len(s)-patindex('%[^0-9 ]%',reverse(s))) as [more progress] , reverse(substring(reverse(s),patindex('%[^0-9 ]%',reverse(s)), 1+len(s)-patindex('%[^0-9 ]%',reverse(s)))) as SOLUTION from @t 

Final answer: reverse (substring (reverse (@s), patindex ('% [^ 0-9]%', reverse (@s)), 1 + len (@s) - patindex ('% [^ 0-9 ]% ', reverse (@s))))

Answer 3

I find the query below quick and useful

 select reverse(substring(reverse(colA),PATINDEX('%[0-9][az]%',reverse(colA))+1, len(colA)-PATINDEX('%[0-9][az]%',reverse(colA)))) from TBLA 

Answer 4

 --DECLARE @String VARCHAR(100) = 'the fat cat sat on the mat' --DECLARE @String VARCHAR(100) = 'the fat cat 2 sat33 on4 the mat' --DECLARE @String VARCHAR(100) = 'the fat cat sat on the mat1' --DECLARE @String VARCHAR(100) = '2121' DECLARE @String VARCHAR(100) = 'the fat cat 2 2 2 2 sat on the mat2121' DECLARE @Answer NVARCHAR(MAX), @Index INTEGER = LEN(@String), @Character CHAR, @IncorrectCharacterIndex SMALLINT -- Start from the end, going to the front. WHILE @Index > 0 BEGIN -- Get each character, starting from the end SET @Character = SUBSTRING(@String, @Index, 1) -- Regex check. SET @IncorrectCharacterIndex = PATINDEX('%[A-Za-z-]%', @Character) -- Is there a match? We're lucky here because it will either match on index 1 or not (index 0) IF (@IncorrectCharacterIndex != 0) BEGIN -- We have a legit character. SET @Answer = SUBSTRING(@String, 0, @Index + 1) SET @Index = 0 END ELSE SET @Index = @Index - 1 -- No match, lets go back one index slot. END PRINT LTRIM(RTRIM(@Answer)) 

NOTE. I have included a dash in valid regular expression.

Answer 5

Thanks for everything that was very helpful. To continue, and pull JUST the final number:

 , substring(s, 2 + len(s) - patindex('%[^0-9 ]%',reverse(s)), 99) as numeric_suffix 

I needed to sort by number suffix, so I had to limit the pattern to numbers and deal with numbers of different lengths sorting as text (i.e. I wanted 2 to sort to 19) produced the result:

 ,cast(substring(s, 2 + len(s) - patindex('%[^0-9]%',reverse(s)),99) as integer) as numeric_suffix