Strip off the trailing periods for me will ya?

Question

Post reply

Strip off the trailing periods for me will ya?

ZenDada

SSCarpal Tunnel

Points: 4637
More actions
February 4, 2009 at 11:09 am

#130592

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]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26969 More actions · Answer 1

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

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

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 3

Mark (2/4/2009)
see if this helps
declare @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]

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]Would a pattern that says "not a period followed by a digit" work? (er, [^.0-9].) Regular expressions are always fun![/font]

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 5

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]