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
  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
  3. FrankKalis Moderator

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

    Thanks. I think BOL has no such example

    Madhivanan

    Failing to plan is Planning to fail
  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>)
  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."

Share This Page