Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL to find first non-numeric character in a string

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.

like image 447
Brad Avatar asked Jan 21 '10 15:01

Brad


1 Answers

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

like image 190
Cade Roux Avatar answered Sep 30 '22 19:09

Cade Roux