filter out alphabetics
-
I need to select a field, pull off only numerics and then put the new numeric value into another field. I've tried to create a user-defined function but a can't concantenate the data. It looks like my concantenation wont work. Am I trying to make it too hard?
-
Create Function Get_Numeric (@InField Varchar(2000)) Returns Varchar(1000) AS
Begin
Declare @Answ Varchar(1000),
@Cnt Int,
@Length Int
Select @Answ='',
@Cnt=1,
@Length=DataLength(@InField)
While @Cnt<=@Length
Begin
If (SubString(@InField,@Cnt,1) between '0' and '9')
Select @Answ = @Answ + SubString(@InField,@Cnt,1)
Set @Cnt=@Cnt+1
End
Return @Answ
End
-
This is similar to the previous except I prefer to use ISNUMERIC function when testing a value.
CREATE FUNCTION dbo.fn_StripAlpha (@val VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @valout VARCHAR(8000)
DECLARE @pos INT
SET @pos = 0
SET @valout = ''
WHILE @pos < DataLength(@val)
BEGIN
SET @pos = @pos + 1
IF ISNUMERIC(SUBSTRING(@val, @pos, 1)) = 1
BEGIN
SET @valout = @valout + SUBSTRING(@val, @pos, 1)
END
END
RETURN @valout
END -
Thanks to 5409045121009 and Antares686. These examples were just what I needed
-
Antares686 how do you structure your code with spaces or tabs.
My formatting always seems to get lost!
-
Antares686 how do you structure your code with spaces or tabs.
My formatting always seems to get lost!
-
quote:
Antares686 how do you structure your code with spaces or tabs.My formatting always seems to get lost!
First I type and tab in QA or Notepad.
Then I get my response ready and type code tag before and after my code if in the middle of my response.
For simpler method do response type in message field then press the # button which is 3 to the left of the smilie button. This will set the code tags at the end then paste your formatted code in the middle of the tags.
-
Just be aware that isnumeric() has it's flaws, depending on the actual charachters you want to filter, this may or may not have an impact on you...
ie isnumeric() considers TAB, CR, LF, operators, comma and dot and monetary chars ($) as numerics too....
A shorter way to write a strip function, and IMHO easier to tailor depending on what you want to filter on, is to use a wildcard range instead of ISNUMERIC()
declare @val varchar(20)
set @val = 'abc13defg345hj'
while PATINDEX('%[^0-9]%', @val) > 0
begin
set @val = REPLACE(@val, SUBSTRING(@val, PATINDEX('%[^0-9]%', @val), 1), '')
end
select @val
--------------------
13345
(1 row(s) affected).. it also saves you some typing by making a strip-function shorter
/Kenneth
-
Just be aware that isnumeric() has it's flaws, depending on the actual charachters you want to filter, this may or may not have an impact on you...
ie isnumeric() considers TAB, CR, LF, operators, comma and dot and monetary chars ($) as numerics too....
A shorter way to write a strip function, and IMHO easier to tailor depending on what you want to filter on, is to use a wildcard range instead of ISNUMERIC()
declare @val varchar(20)
set @val = 'abc13defg345hj'
while PATINDEX('%[^0-9]%', @val) > 0
begin
set @val = REPLACE(@val, SUBSTRING(@val, PATINDEX('%[^0-9]%', @val), 1), '')
end
select @val
--------------------
13345
(1 row(s) affected).. it also saves you some typing by making a strip-function shorter
/Kenneth
-
That had not occurred to me, thanks for pointing out.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply