SET NOCOUNT ON

DECLARE @x varchar(max); SET @x = 'abc12efg34'
DECLARE @t table (seq int IDENTITY(1,1), c char(1))
DECLARE @l int; SET @l = LEN(@x)
DECLARE @n int; SET @n = 1
DECLARE @numStr varchar(max); SET @numStr = ''

WHILE @n < @l + 1
  BEGIN
	INSERT INTO @t(c) SELECT SUBSTRING(@x,@n,1)
	SET @n = @n + 1
  END

  SELECT @numStr = @numStr + c 
    FROM @t 
   WHERE c IN ('1','2','3','4','5','6','7','8','9','0')
ORDER BY seq

SELECT @numStr

SET NOCOUNT ON

declare @x varchar(max); SET @x = 'abc12efg34'
declare @numbers varchar(max)=''

select @numbers=@numbers + '' + number from 
(
select 
	convert(varchar(2),substring(@x,number,1))as number
	from master.dbo.spt_values where type='p'
	and number between 1 and LEN(@x)
)t where number between '0' and '9'

select @numbers as numbers

quote:

---

Originally posted by Idera

I have simplified madhivanan's solution a little bit.Hope he does not feel offended :)

declare @x varchar(max); SET @x = 'abc12efg34'
declare @numbers varchar(max)=''

select @numbers=@numbers + '' + number from 
(
select 
	convert(varchar(2),substring(@x,number,1))as number
	from master.dbo.spt_values where type='p'
	and number between 1 and LEN(@x)
)t where number between '0' and '9'

select @numbers as numbers

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

---

quote:

---

Originally posted by madhivanan


No Problem

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by X002548

Wel...that's pretty darn close...

but why

and number between 1 and LEN(@x)


???

why not 0 and 9..you're still looking at 1 byte at a time

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---

;WITH CTE(i) AS
(SELECT 'sadlfjks 213 dfj 234324 sf' UNION ALL
 SELECT '2342sadfwe4234sdf9o7234' UNION ALL
 SELECT '2432sfsf234')
 
 SELECT D.i
   FROM CTE
        CROSS APPLY
        (SELECT i + ''
           FROM (SELECT SUBSTRING(i, n, 1)
                   FROM Numbs /* Tally Tables */
                  WHERE n <= LEN(i)
                 )D(i)
          WHERE i LIKE '[0-9]'
          FOR XML PATH(''))D(i);
/*
i
-----------------
213234324
2342423497234
2432234
*/