Re: Stipping all alpha characters from a string

• Previous message: Alyx: "Stipping all alpha characters from a string"
• In reply to: Alyx: "Stipping all alpha characters from a string"
• Messages sorted by: [ date ] [ thread ]

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

• Previous message: Alyx: "Stipping all alpha characters from a string"
• In reply to: Alyx: "Stipping all alpha characters from a string"
• Messages sorted by: [ date ] [ thread ]