SQL Server Performance

' quote problem in sql query

Discussion in 'General Developer Questions' started by pankaj.indore, May 19, 2006.

  1. pankaj.indore New Member

    hi i am using the visual basic 6.0 and sql server 200 database
    my client want to enter the single quote (') and
    double qoute (") in the database
    because as u know in hardware filed all measurment goes into the
    inches like 10 inch can be 10' or 10" how i can manage this?
    because in the VB 6.0 both are used for close the string....[?]


    regards pankaj joshi
    pankaj.indore, May 19, 2006
    #2

  2. Madhivanan Moderator

    Instead of using single quote use it twice

    Select 'test'

    ----
    test

    (1 row(s) affected)

    Select '''test'''

    ------
    'test'

    (1 row(s) affected)



    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, May 19, 2006
    #2

  3. FrankKalis Moderator

    FrankKalis, May 19, 2006
    #2

  4. Adriaan New Member

    If you spell-out a string literal in VB code, then it must start and end with a double-quote character.

    If you are compiling a string in VB code, then you can use Chr(34) to include a double-quote " character within the string, or Chr(39) to include a single-quote character (').

    If you are compiling a T-SQL statement in VB code, to be forwarded to SQL Server, then it depends on what exactly you are doing.

    If this will be a complete T-SQL query statement, then you only have to worry about literal criteria, for example:
    SELECT name FROM tblNames WHERE name LIKE 'McDonald''s'

    Note the duplicated single quote!

    Once you start using dynamic T-SQL, you will run into issues where you must duplicate all single-quotes, even those that are already duplicated.
    Adriaan, May 19, 2006
    #2

  5. Madhivanan Moderator

    quote:Originally posted by FrankKalis

    Another way would be to use:


    SELECT QUOTENAME('test', '''')

    --------------------
    'test'

    (1 row(s) affected)


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
    Why are the following return same 'test'?

    SELECT QUOTENAME('test', '''''')
    SELECT QUOTENAME('test', '''''''')
    SELECT QUOTENAME('test', '''''''''')


    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, May 19, 2006
    #2

  6. FrankKalis Moderator

    BOL states that QUOTENAME returns a delimited string. Adding more ' sigsn to the quote_character parameter doesn't yield you a more delimited string, so I guess they are simply ignored by SQL Server. However it is different when you do something like


    SELECT QUOTENAME('''test''', '''')



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, May 19, 2006
    #2

  7. Madhivanan Moderator

    Thanks. I think BOL has no such example

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, May 19, 2006
    #2

  8. FrankKalis Moderator

    Nope. Why should it? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
    FrankKalis, May 19, 2006
    #2

  9. Jeya prabhu New Member

    Hello Pankaj, try this one


    select '"' + employee_name + '"' EmployeeName from employee_master

    EmployeeName
    ---------------
    "Prabhu"



    S.Jeya Prabhu

    "A ship in Harbour is safe,
    But that is not what ships are built for."
    Jeya prabhu, May 19, 2006
    #2

Share This Page