Re: Stipping all alpha characters from a string
From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/27/04
- 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 ]
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: 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) - 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)