9

I inherited a table with identifiers in a format [nonnumericprefix][number]. For example (ABC123; R2D2456778; etc). I was wondering if there was a good way to split this in SQL into two fields, the largest integer formed from the right side, and the prefix, for example (ABC, 123; R2D, 2456778; etc). I know I can do this with a cursor, C# code, etc - and I will if I have to - but I don't run into things I cannot do fast and easily in SQL very often, so I thought I'd post it here.

Brad
  • 1,360
  • 4
  • 18
  • 27
  • BTW - I just realized I asked my topic as the "to find first non-numeric character" and gave an example asking to find the last non-numeric. Reversing a string is trivial, so this doesn't change the nature of the question. – Brad Jan 21 '10 at 15:49
  • Better edit the question as oppose to clarify it with comment. – Sunny Milenov Jan 21 '10 at 15:53
  • Did not know about PATINDEX, this is useful, thanks! – Brad Jan 21 '10 at 20:23

3 Answers3

18

You can use PATINDEX with a pattern like '%[^0123456789]%' or '%[^0-9]%' to find the position of the first non-numeric character

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
17
  1. Reverse the string
  2. Use PATINDEX to find the first occurrence of a non numeric field
  3. Use the LEFT function to return the numeric portion of the string

Code sample 

DECLARE @myString varchar(100);
DECLARE @largestInt int;

SET @myString = 'R2D2456778'

SET @mystring = REVERSE(@myString);
SET @largestInt = LEFT(@myString, PATINDEX('%[a-z]%', @myString) - 1)

PRINT ( CONVERT(varchar(100), @largestInt) )
Cory
  • 12,404
  • 7
  • 33
  • 28
8

You could try something like

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

INSERT INTO @Table SELECT 'ABC123'
INSERT INTO @Table SELECT 'R2D2456778'

SELECT  *,
        LEFT(Val,LEN(Val) - (PATINDEX('%[^0-9]%',REVERSE(Val)) - 1)),
        RIGHT(Val,(PATINDEX('%[^0-9]%',REVERSE(Val)) - 1))      
FROM    @Table
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • +1 looks good, would benefit from a cast to `int` on third column. – D'Arcy Rittich Jan 21 '10 at 16:24
  • 1
    Possibly not an issue for the OP, but I had a problem with `[^0-9]` matching alt codes (like ¼, etc.) and then failing the cast to an integer. Using `[^0123456789]` instead, for some reason, fixed the issue. – Hannele Sep 17 '12 at 17:58