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

*** Sent via Developersdex http://www.developersdex.com ***

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


*** Sent via Developersdex http://www.developersdex.com ***

On 29 Sep 2006 20:31:59 GMT, Terry Olsen wrote:

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

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

--
Hugo Kornelis, SQL Server MVP