April 18, 2008 at 7:32 am
Hi,
I have a table that contains payee details. The problem is that some payee's have apostrophies in their name (eg O'neill, O'Rourke etc)
everytime the code to rec these off is run it falls over everytime it hits one of these payees as it thinks the ' is the end of the string.
What i have written so far is
select Table.Payee
Set Table.Payee = Replace (Table.Payee,"''",'`')
From Table
Where Table.Payee like '%''%'
and i think that this should work, but i keep getting Line 2: Incorrect syntax near '`'
Has anyone got any way of working around this? The ' is mid string and each string is potentially different so i can't just enter it on it's own.
Thanks
April 18, 2008 at 7:41 am
What do you actually want to do? Update the underlying data? Select the data but change it slightly? What do you want to change it from and to?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 18, 2008 at 7:46 am
The problem is not with single quotes, but with the way you insert data intto database. This issue arises when concatenation of queries is used instead of parameters. Moreover, your database may be susceptible to SQL injection attacks.
Database should be data-agnostic (is that the right word?) which means that you should be able to store any data in it.
I'd recommend you reviewing all queries that you have in your application and refactor them to use parameters.
Piotr
...and your only reply is slàinte mhath
April 18, 2008 at 7:47 am
Hi,
Apologies, i want to update the data, so that all strings in Table.Payee that have an ' are changed to have an ` instead so they wont disrupt the running of the other queries.
so where the field might be Mrs A O'Rourke it gets updated to Mrs A O`Rourke.
the problem is that this field will always be different.
April 18, 2008 at 7:50 am
Hi,
This would be a great idea only the program that this is a problem with is not ours.
We have an outside software supplier that has written this for us and we have a user end module. They are changing the program to make sure that all info put in this table changes the ' to the ` in future, however i have to tidy up the current data to stop this happening before the fix is in place.
I have no way of changing the module used 🙁
April 18, 2008 at 7:58 am
though i would usually look for another solution, dealing with third party consumers of data some times takes compromise
try this code
update
set [payee] = replace([payee],'''','`')
April 18, 2008 at 8:05 am
Your initial code was almost an UPDATE statement, not sure how you got the error you say you got from running the code you posted as it was neither a SELECT nor an UPDATE statement, sort of a love-child they may have had... 😀
Anyway, this is how it should have looked, and this should work fine:
UPDATE Table
SET Payee = Replace (Payee,'''','`')
WHERE Payee like '%''%'
EDIT: Sorry Steve, didn't see your response before posting!!
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 18, 2008 at 8:09 am
Hello,
You could try using the Ascii Characters like this:
Select Payee, Replace(Payee,Char(39),Char(96))
From Payee
Where Charindex(Char(39),Payee) > 0
I usually do this to work out what the character codes should be:
Declare @Count int, @Limit Int
Set @Count = 33
set @Limit = 255
While @Count <= @Limit
Begin
Select Cast(@Count as Varchar(3)),Char(@Count)
Set @Count = @Count + 1
End
Hope that helps!
April 18, 2008 at 8:16 am
Hi All,
Thanks so much for your help, much appreciated!
I Tried Rob's and it works perfectly!!!!
Thank you all!!!
hehehehe, dead pleased now!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply