sql server (alternate): 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...

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]?

[quoted text, click to view]

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).

--