Re: Stipping all alpha characters from a string

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/27/04

  • Next message: Steve Kass: "Re: Multiple Server query" 
    Date: Wed, 28 Apr 2004 02:36:34 +0530

      hi alyx,

      if i understand you correctly, you will have to make use of user defined
    function.

      ex:
      --function

      create function dbo.fn_numeric (@strn as varchar(8000))
      returns varchar(8000)
      AS
      begin
        WHILE PATINDEX('%[^0-9]%', @strn) > 0
        SET @strn = REPLACE(@strn,SUBSTRING(@strn,PATINDEX('%[^0-9]%',
    @strn),1),'')
      RETURN @strn
      end
      GO

      --sample data

      create table t(strn varchar(50))
      insert into t select 'call me' union all
      select '1 (555) 555-8888' union all
      select 'please email' union all
      select '+049.890.9876' union all
      select '0x0x0x' union all
      select 'Work: (444) 123-4567'

      --usage of above function
      select strn, dbo.fn_numeric(strn) 'numeric'
      from t
      where isnumeric (dbo.fn_numeric (strn)) = 1

      --
      Vishal Parkar
      vgparkar@yahoo.co.in

      "Alyx" <alyxk@labx.com> wrote in message
    news:5a70dddd.0404271211.5669d2b7@posting.google.com...
    > I need to strip all characters that are not a number from a string
    > variable in a sql procedure. There is not clear pattern to the
    > character the user can input.
    >
    > Example input for the phone field
    > 1. call me
    > 2. 1 (555) 555-8888
    > 3. please email
    > 4. +049.890.9876
    > 5. 0x0x0x
    > 6. Work: (444) 123-4567
    >
    > I need to strip the characters down to only a number so I can evalute
    > if a correct entry was made.
    >
    > After stripping and checking above example numbers 2,4, and 6 would be
    > the only examples that would come back valid.
    >
    > Any help would be appreciated.
    >
    > Thanks,
    >
    > Alyx

  • Next message: Steve Kass: "Re: Multiple Server query"

    Relevant Pages