0

Using SQL 2008

ID: C/213434343434334, CA/2566121212122

From the above id, i want to take only numbers. How to acheive this

Required Output

213434343434334, 2566121212122

Need Query Help

Gopal
  • 11,712
  • 52
  • 154
  • 229
  • It does appear to be a duplicate, but I can't see any reference in that question to using CLR, which I think is the best solution. [This page](http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx) contains an example for `RegexReplace` using CLR. – GarethD Dec 01 '14 at 11:38

4 Answers4

2

try this way

DECLARE @str VARCHAR(400)
DECLARE @expres  VARCHAR(50) = '%[a-z,/]%'
SET @str = 'CA/2566121212122'
WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')

SELECT @str
Hiral Nayak
  • 1,062
  • 8
  • 15
1
 SELECT substring(ID,charindex('/',ID)+1,len (ID)-charindex('/',ID))  New_Id
FROM [YOUR TABLE]
Dudi Konfino
  • 1,126
  • 2
  • 13
  • 24
0

If all characters will be in front of the special character '/', you can use the following:

DECLARE @ID1 nvarchar(200) = 'C/213434343434334', 
        @ID2 nvarchar(200) = 'CA/2566121212122'


SELECT RIGHT(@ID1, CHARINDEX('/', REVERSE(@ID1))-1)
SELECT RIGHT(@ID2, CHARINDEX('/', REVERSE(@ID2))-1)

RESULTS:

RESULT @ID1 : 213434343434334
RESULT @ID2 : 2566121212122

Hope this helps

PKirby
  • 859
  • 3
  • 16
  • 36
0

Use SUBSTRING() to get the result.

CREATE TABLE #Test
(
 ID NVARCHAR(MAX) 
)

INSERT INTO #Test
SELECT 'C/213434343434334' UNION
SELECT 'CA/2566121212122'

SELECT SUBSTRING(ID, CHARINDEX('/', ID)+ 1, LEN(ID)) AS ID FROM #Test
Saravana Kumar
  • 3,669
  • 5
  • 15
  • 35