Strip off the trailing periods for me will ya?
-
create function dbo.udf_KeepNumOrPeriod
(
@string varchar(5000)
)
returns varchar(5000)
as
begin
while patindex('%[^0-9,.]%', @string) > 0
set @string = replace(@string,substring(@string,patindex('%[^0-9,.]%', @string),1),'')
return @string
end
select dbo.udf_KeepNumOrPeriod('$4.21 plus tax')
--4.21
select dbo.udf_KeepNumOrPeriod('$4.21 plus tax....')
--4.21.... <<<< I wanna get rid of the trailing periods.
[font="Courier New"]ZenDada[/font]
-
see if this helps
declare @string varchar(30)set @string = '$4.21 plus tax....'
select left(@string,1+len(@string)-patindex('%[^.]%',reverse(@string)))
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark your T-SQL returns
$4.21 plus tax
not what I think the user wants
ZenDada - you can try this but note it will NOT function as you requested if the text "plus tax" is followed by a single (1) period or any other pattern that starts with other than at least 2 consecutive periods.
ALTER function dbo.udf_KeepNumOrPeriod(
@string varchar(5000)
)
returns varchar(5000)
as
begin
DECLARE @periods INT
SET @periods = 0
while patindex('%[^0-9,.]%', @string) > 0
set @string = replace(@string,substring(@string,patindex('%[^0-9,.]%', @string),1),'')
SET @periods = CHARINDEX('..',@String)
IF @Periods > 0
Begin
SET @string = SUBSTRING(@string,1,@periods - 1)
END
return @string
end
-
Mark (2/4/2009)
see if this helpsdeclare @string varchar(30)set @string = '$4.21 plus tax....'
select left(@string,1+len(@string)-patindex('%[^.]%',reverse(@string)))
Sweet Thanks!!!!!
[font="Courier New"]ZenDada[/font]
-
[font="Verdana"]Would a pattern that says "not a period followed by a digit" work? (er, [^.0-9].) Regular expressions are always fun![/font]
-
Bruce W Cassidy (2/4/2009)
[font="Verdana"]Would a pattern that says "not a period followed by a digit" work? (er, [^.0-9].) Regular expressions are always fun![/font]Oh heck yeah I like that solution!
[font="Courier New"]ZenDada[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply