SQL Replace comma with apostrophe, comma, apostrophe

Databases
1

Hi

I have a data type problem and I need to use an SQL Replace to tidy up my only the quotes/apostrophes are driving me nuts:

I have a data value PR.Type which is a string value of the form 1, 2, 3

And I need to test if a numeric value (ie: 1) is in this string


SELECT firstname, surname, email 
FROM clients as C INNER JOIN propertyrequirements as PR 
ON C.clientid = PR.clientid 
WHERE 1 IN  (PR.type)

Yields a datatype mismatch. I need to adjust my WHERE clause so the values I’m comparing are of the same datatype to do this I need to

(a) wrap the ‘1’ in single quotes and (b) adjust the data in the PR.Type field so each of the comma separated values are also enclosed in single quotes.

I was hoping to be able to do this with a Replace function, but I’m getting know where fast and suffering from quote confusion.

Here’s where I’ve got to:


SELECT firstname, surname, email 
FROM clients as C INNER JOIN propertyrequirements as PR 
ON C.clientid = PR.clientid 
WHERE '1' IN  (''' & Replace(PR.type,',', '', '' ) & ''')

Am I going about this the right way? Have just got in a quoted mess?

2

once again, you neglected to mention which database system you’re using (this forum is for all of them except mysql)

so i’m going to go by the evidence right in your query and guess MS Access

what you want is this –

WHERE ','&PR.type&',' LIKE '*,1,*'

by the way, because of your comma-separated ids column this query will not scale, i.e. the more rows you have, the slower this query will get

3

Hi r937

Thanks for your help, but you are right this isn’t going to scale, I have several other values I need to compare in the same WHERE clause all using similar comma separated ids. If I normalised the database would this allow the query to scale?

4

almost certainly :slight_smile:

comma-separated values aren’t all that bad ~UNLESS~ you want either to search for a value within them, or to join each of them to, say, a lookup table

if you are 100% sure you will never, ever need to do either of those things, then they’re okay

5

In other words, just the kind of things I’ve been trying to do.

Its a pig really, I’d have never designed this database this way. The developer who had this before me obviously saw the merits of just taking a comma separated list of field values and dumping them into the insert and update routine writing the Property Requirements values into a single table. But now I’m been asked to do something useful with this data its proving a proper pain in the butt.