While PatIndex

Question

While PatIndex

kstephens 90242

Old Hand

Points: 350
More actions
April 11, 2012 at 1:22 pm

#391992

Help please,
I have this code that uses patindex and I want to loop through it to do a replace.
Declare @idx_Operator nvarchar(10)
Declare @Operator nvarchar(10)
Declare @formula nvarchar(100)
select @formula = 'BRIDGESTONE+FIRESTONE perelli-MyTire'--formula from goal_formulas where id = 1
while PATINDEX('%[+-/*|]%', @formula) > 0
begin
SELECT @idx_Operator=PATINDEX('%[+-/*|]%', @formula)
,@Operator=LTrim(RTrim(substring(@formula,patindex('%[+-/*|]%',@formula),1)))
set @formula = replace(@formula, @Operator, ' ')
end
select @idx_Operator as idx_Operator, @Operator as operator,@formula
If you run the following you just get back BRIDGESTONE+FIRESTONE perelli-MyTire
It didn't seem to get the + and -?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

I think your problem is the patindex. The expression is not right... Always use SELECT first to make sure you are getting from your PATINDEX what you think you are.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 2

Actually... start with this:

Declare @idx_Operator nvarchar(10)

Declare @Operator nvarchar(10)

Declare @formula nvarchar(100)

select @formula = 'BRIDGESTONE+FIRESTONE perelli-MyTire'--formula from goal_formulas where id = 1

SELECT PATINDEX('%[+-/*|]%', @formula)

SELECT PATINDEX('%[+%-%/%*%|]%', @formula)

See?

Jared
CE - Microsoft

kstephens 90242 Old Hand Points: 350 More actions · Answer 3

kstephens 90242

Old Hand

Points: 350

April 11, 2012 at 1:35 pm

#1472193

Gotcha Thanks Keith.

Jared SSC Guru Points: 61793 More actions · Answer 4

No problem, but I am certainly not an expert (or even the least bit knowledgeable) on REGEXs. Should look at this a bit more.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 5

Hmm... That won't work either. Take out the + from the string and leave in the -, then the result of the patindex returns 0 again. Hmm...

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 6

This fixes the patindex, can't start it with a - in the regex. However, your query only displays the last character replaced. Is that what you want?

Declare @idx_Operator nvarchar(10)

Declare @Operator nvarchar(10)

Declare @formula nvarchar(100)

select @formula = 'BRIDGESTONE+FIRESTONE perelli-MyTire'--formula from goal_formulas where id = 1

while PATINDEX('%[-+*/|]%', @formula) > 0

begin

SELECT @idx_Operator=PATINDEX('%[-+*/|]%', @formula)

,@Operator=LTrim(RTrim(substring(@formula,patindex('%[-+*/|]%',@formula),1)))

set @formula = replace(@formula, @Operator, ' ')

end

select @idx_Operator as idx_Operator, @Operator as operator,@formula

Jared
CE - Microsoft

kstephens 90242 Old Hand Points: 350 More actions · Answer 7

kstephens 90242

Old Hand

Points: 350

April 11, 2012 at 2:08 pm

#1472220

That's it. Thanks alot.