SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Replace comma with apostrophe, comma, apostrophe

    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
    Code:
    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:

    Code:
    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?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SortedSites View Post
    If I normalised the database would this allow the query to scale?
    almost certainly

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    almost certainly

    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
    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.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •