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.
3
-
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– Brad2010-01-21 15:49:33 +00:00Commented Jan 21, 2010 at 15:49
-
Better edit the question as oppose to clarify it with comment.Sunny Milenov– Sunny Milenov2010-01-21 15:53:38 +00:00Commented Jan 21, 2010 at 15:53
-
Did not know about PATINDEX, this is useful, thanks!Brad– Brad2010-01-21 20:23:55 +00:00Commented Jan 21, 2010 at 20:23
Add a comment
|
3 Answers
You can use PATINDEX with a pattern like '%[^0123456789]%' or '%[^0-9]%' to find the position of the first non-numeric character
Comments
- Reverse the string
- Use PATINDEX to find the first occurrence of a non numeric field
- 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) )
2 Comments
rosscj2533
Nice solution, but to get the original int part of myString, you'll need to reverse largestInt.
Adriaan Stander
This will fail with anything other thatn a-z, try R2D245.6778
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
2 Comments
D'Arcy Rittich
+1 looks good, would benefit from a cast to
int on third column.
Hannele
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.