How can I get a number from an nvarchar

I have a table called Foo and there is a column named Bar, which is an nvarchar.

The values of Bar look like this: Prefix + Number

Examples:

Bar = 'abc123'
Bar = 'a1bc23'

I would like to select the Number part (second half) of Bar, which would be:

123 if Bar = 'abc123'
23 if Bar = 'a1bc23'

Presuming that the prefix doesn't end with a number how can I get t开发者_如何转开发he number part of Bar?

NOTES:

• We don't know the length of the prefix
• Either a T-SQL or a LINQ solution would be fine.

Thank you in advance for your time.

---

WITH T(C)
AS
(
SELECT 'a1bc23' UNION ALL 
SELECT 'abc123' UNION ALL 
SELECT 'FOO'    UNION ALL 
SELECT  NULL    UNION ALL 
SELECT '456' 
)
SELECT 
      CASE WHEN C LIKE '%[0-9]' 
           THEN CASE WHEN C LIKE '%[^0-9]%' 
                     THEN RIGHT(C,PATINDEX('%[^0-9]%', REVERSE(C))-1) 
                     ELSE C
                 END
      END AS Number
FROM T 

Returns

Number
------
23
123
NULL
NULL
456

---

Martin has a good answer for how to do it. While I'm sure this would work, it could turn out to be an expensive call, since you're doing a couple operations on a string field. If you have 1000s or millions of rows, this might not be performant.

I might suggest looking at the why you need to do this, and see if you can format your data in a way that would make it easier to handle this situation. Split the one field into 2 and have a prefix field and suffix field or something along those lines. Especially if these are "smart part numbers" or something along those lines, you would be able to do group by syntax easier.