how to query for text containing parens?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Terry Olsen - 29 Sep 2006 21:13 GMT

I have an SQL database with rows that have parens in the data.

If I run a select statement such as:

SELECT SongName
FROM Songs
WHERE SongName = 'John Jacob (Jingleheimer Schmidt)'

It returns zero rows. This also:

SELECT SongName
FROM Songs
WHERE SongName LIKE '%John Jacob (Jingleheimer Schmidt)%'

returns zero rows.

If I change it to this:

SELECT SongName
FROM Songs
WHERE SongName LIKE '%John Jacob%'

Then I get the row returned.

Is there a way to use the first query example above and return the row?
I'm guessing it has something to do with the parenthesis...

Reply to this Message

Terry Olsen - 29 Sep 2006 21:31 GMT

I figured out what the problem is, now how to come up with a solution.

The problem seems to be that the last [space] is being represented with
A0 instead of 20 in the database.

How can I take that into account in my queries and return the data
regardless of the byte value used for [space]?

Reply to this Message

Hugo Kornelis - 29 Sep 2006 22:28 GMT

>I figured out what the problem is, now how to come up with a solution.
>
[quoted text clipped - 3 lines]
>How can I take that into account in my queries and return the data
>regardless of the byte value used for [space]?

Hi Terry,

Short-term solution:

WHERE REPLACE (SongName, CHAR(160), ' ') = 'John Jacob (Jingleheimer
Schmidt)'

Downside is that an index on the SongName column (if there is any) can't
be used as effectively.

Long-term solution: fix the front end or the stored proc that handles
data entry to convert char(A0) to space (= fixing the leak), then run an
update to convert existing data (= mopping up the floor).

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message