how to return only numeric chars from column?

8 Sep 2006 9:01 PM

Rich

Hello,

How can I return only the numeric chars of this data?

CREATE TABLE #temp1 (col1 varchar(10))
INSERT INTO #temp1
SELECT 'abc123' union
SELECT 'defg1234'

Thanks
Rich

8 Sep 2006 9:10 PM

Aaron Bertrand [SQL Server MVP]

You could do this with a function. There is nothing built-in that will do
this very easily.

CREATE FUNCTION dbo.ParseInt
(
    @s VARCHAR(10)
)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @i TINYINT, @newS VARCHAR(10);
    SELECT @i = 1, @newS = '';
    WHILE @i <= LEN(@s)
    BEGIN
        IF SUBSTRING(@s, @i, 1) BETWEEN '0' AND '9'
            SET @newS = @newS + SUBSTRING(@s, @i, 1);
        SET @i = @i + 1;
    END
    IF @newS = ''
        SET @newS = NULL;
    RETURN (@newS);
END
GO

CREATE TABLE #temp1 (col1 varchar(10));

SET NOCOUNT ON;

INSERT INTO #temp1
SELECT 'abc123' union
SELECT 'defg1234' union
SELECT 'degrassi';

SELECT dbo.ParseInt(col1)
FROM #temp1
--WHERE dbo.ParseInt(col1) IS NOT NULL;

DROP TABLE #temp1;
DROP FUNCTION dbo.ParseInt;

Show quote

"Rich" <R***@discussions.microsoft.com> wrote in message
news:AECB3E4B-A460-4609-9E90-973EA60D64A7@microsoft.com...
> Hello,
>
> How can I return only the numeric chars of this data?
>
> CREATE TABLE #temp1 (col1 varchar(10))
> INSERT INTO #temp1
> SELECT 'abc123' union
> SELECT 'defg1234'
>
> Thanks
> Rich

8 Sep 2006 9:30 PM

Rich

Thank you. This was the route I was originally thinking, but since I have a
history of re-inventing the wheel, I was basically checking to see if there
were other options. I guess not for this case. BTW, nice function;

Rich

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> You could do this with a function. There is nothing built-in that will do
> this very easily.
>
>
> CREATE FUNCTION dbo.ParseInt
> (
>     @s VARCHAR(10)
> )
> RETURNS VARCHAR(10)
> AS
> BEGIN
>     DECLARE @i TINYINT, @newS VARCHAR(10);
>     SELECT @i = 1, @newS = '';
>     WHILE @i <= LEN(@s)
>     BEGIN
>         IF SUBSTRING(@s, @i, 1) BETWEEN '0' AND '9'
>             SET @newS = @newS + SUBSTRING(@s, @i, 1);
>         SET @i = @i + 1;
>     END
>     IF @newS = ''
>         SET @newS = NULL;
>     RETURN (@newS);
> END
> GO
>
> CREATE TABLE #temp1 (col1 varchar(10));
>
> SET NOCOUNT ON;
>
> INSERT INTO #temp1
> SELECT 'abc123' union
> SELECT 'defg1234' union
> SELECT 'degrassi';
>
> SELECT dbo.ParseInt(col1)
> FROM #temp1
> --WHERE dbo.ParseInt(col1) IS NOT NULL;
>
> DROP TABLE #temp1;
> DROP FUNCTION dbo.ParseInt;
>
>
>
>
>
> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:AECB3E4B-A460-4609-9E90-973EA60D64A7@microsoft.com...
> > Hello,
> >
> > How can I return only the numeric chars of this data?
> >
> > CREATE TABLE #temp1 (col1 varchar(10))
> > INSERT INTO #temp1
> > SELECT 'abc123' union
> > SELECT 'defg1234'
> >
> > Thanks
> > Rich
>
>
>