[Solved] SQL LOCATE to find an apostrophe

[Nocton]

I am trying to find the apostrophe (') in names such as O'Hara so that I can remove them because a government database to which I need to submit data will not accept them.
So I want to find the apostrophe and then remove it, i.e. O'Hara to OHara.
As a first step I tried to use a LOCATE statement in my query, checking if a non-zero value is returned. The statement I used was:
LOCATE( CHAR( 39 ), "LastName" ) where 39 is the ASCII value for the apostrophe.
This returned zero even when an apostrophe was present. I have checked with other characters (letters, -) and it works OK. Can anyone help, please?

Regards

Nocton

[Villeroy]

Which database software?
HSQL?
http://www.hsqldb.org/doc/1.8/guide/ch09.html

REPLACE(s,replace,s2)

replaces all occurrences of replace in s with s2

May be, the second character in O´Hara is not char(39)?

[Sliderule]

I would like to agree with user Villeroy, when stated that perhaps, the character you have in the text field is not a ', but, perhaps a ´ .

Just so you know, besides using CHAR(39) in the LOCATE function, you could in your example have coded it as:

Code: Select all

LOCATE('''', "LastName" )

Explanation: Since the first parameter of the LOCATE function is a string, the string is surrounded by single quotes. And, since, you want the contents of the string to be a single quote, by using TWO single quotes ( '' ) this means it will convert them to a single quote. Bottom line, four single quotes as the first parameter to denote one single quote.

I hope that is clear, well, at least as clear as mud.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[Nocton]

Thank you, Villeroy. That is a neater solution to the overall problem. However, just as with LOCATE the apostrophe/CHAR(39) was not found. As before it worked with letters and the hyphen(-).

So like you I surmised that perhaps the apostrophe was not character 39. But it seemed to be. I've checked it with an ASC function in Basic and it returns 39. I've also typed it from the keyboard and using the Alt key and number pad - exactly the same character is produced. However, further checking showed that if I enter as Alt/39 the character is found, but not if I enter from the keyboard!

I've just seen Sliderule's solution come up and I've tried that, but exactly the same.

Thinking that this is really odd, I have gone back to the table and retyped the apostrophes from the key board. All worked OK. I then extracted the apostrophe from the field and found its ASCII value to be 0146! Both 39 and 146 look exactly the same on a Base form. I think the original data was imported from an MS Excel or Word table when the apostrophe must have been converted to a closing single quote mark, as normal keyboard typing cannot add that character?

At any rate all solved and thank you both for your help. I was thinking that the problem was something to do with apostrophes being delimiters for strings and fields.

Regards

Nocton

[Villeroy]

Code: Select all

SELECT "T"."N", ASCII(SUBSTR("N",2,1))AS "ASCII@Pos 2" FROM "T"

N	ASCII@Pos 2
-----------------------
O'Hara	39
O`Hara	96
O´Hara	180
O¨Hara	168
O"Hara	34
O²Hara	178

[Villeroy]

Code: Select all

select distinct substr("N",2,1) AS "S" FROM "T" WHERE NOT ASCII(substr("N",2,1)) BETWEEN 97 AND 122

selects unique 2nd characters that are no lower latin letters

If you want to eliminate all those 2nd characters in the table field:

Code: Select all

update "T" SET "N"=LEFT("N",1)||SUBSTR("N",2) WHERE NOT ASCII(substr("N",2,1)) BETWEEN 97 AND 122

If the above query includes some wanted characters, modify the WHERE clause of the update statement
WHERE (NOT ASCII(substr("N",2,1)) BETWEEN 97 AND 122) AND (substr("N",2,1) != 'â') AND (substr("N",2,1) != 'ô')