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

    • Re: RunTime Datatype Determination in C
      ... Richard Tobin wrote: ... a union of the possible types, ... "Consideration shall be given to the need for as many as 32 characters ... in some alphabets" - X3.4, ...
      (comp.lang.c)
    • Re: Newbie question on unions
      ... patleonard wrote: ... > Characters: XY ... > union test_union{ ... > short int i; ...
      (comp.lang.cpp)
    • Re: help neeed statement
      ... length would not exceed 8000 characters: ... from sysobjects ... > i need to create a union select statement with all tables that have 'Mail' ... > how would i creat the statement? ...
      (microsoft.public.sqlserver.programming)
    • Re: field limit
      ... You can try switching to a UNION ALL query vice a UNION query. ... The union is finding only distinct rows of data and to do so it automatically ... truncates the Description to 255 characters. ... BUT looking at your query I don't understand why you are using a UNION query at all. ...
      (microsoft.public.access.queries)
    • Re: selecting Fields for Report
      ... but it seems I can only create a union for the first sql statement ... I keep getting errors 'Characters after end of sql staement' or syntax ...
      (microsoft.public.access.reports)