Professional Web Applications Themes

Formatting string in WHERE Clause - Microsoft SQL / MS SQL Server

It doesn't work that way... IN expects a list of expressions either constant expression, variables or a 'select' of a column from a table. When you ask IN (criteria) it will look for a match with the whole criteria as a string and will not treat it as a list of expressions. You could try : DECLARE criteria TABLE (col1 char(8)) INSERT INTO Criteria VALUES('LSACONXB') INSERT INTO Criteria VALUES('ALTLCNXB') INSERT INTO Criteria VALUES('CAGECDXH') INSERT INTO Criteria VALUES('REFNUMHA') --And Then Use: AND (Static.dbo.intField.FieldName IN (SELECT col1 FROM Criteria)) HTH Amy. "Corrie Taljaard" <co.za> wrote in message news:bf8h8j$cgb$saix.net...  +  ('LSACONXB','ALTLCNXB','CAGECDXH')) ...

  1. #1

    Default Re: Formatting string in WHERE Clause

    It doesn't work that way...
    IN expects a list of expressions either constant expression, variables or a
    'select' of a column from a table.
    When you ask IN (criteria) it will look for a match with the whole
    criteria as a string and will not treat it as a list of expressions.
    You could try :

    DECLARE criteria TABLE (col1 char(8))
    INSERT INTO Criteria VALUES('LSACONXB')
    INSERT INTO Criteria VALUES('ALTLCNXB')
    INSERT INTO Criteria VALUES('CAGECDXH')
    INSERT INTO Criteria VALUES('REFNUMHA')

    --And Then Use:

    AND (Static.dbo.intField.FieldName IN (SELECT col1 FROM Criteria))

    HTH

    Amy.





    "Corrie Taljaard" <co.za> wrote in message
    news:bf8h8j$cgb$saix.net... 

    ('LSACONXB','ALTLCNXB','CAGECDXH')) 


    Amy Guest

  2. #2

    Default Formatting string in WHERE Clause

    Hi ,
    I'm having a problem formatting a string to be used in the WHERE clause.
    Here is my code

    DECLARE Criteria varchar(700)

    'The formatting that i tried without success
    Using 2 ( ' ) hypens next to each other
    SET Criteria = ' ' + ''LSACONXB'' + ',' + ''ALTLCNXB'' + ',' +
    ''CAGECDXH''+ ',' + ''REFNUMHA'' + ' '

    Using ( " ) double quotes

    SET Criteria = ' ' + "LSACONXB" + ',' + "ALTLCNXB" + ',' + "CAGECDXH" +
    ',' + "REFNUMHA" + ' '

    Using a combination of ( " ) double quotes and ( ' ) hypen
    SET Criteria = ' ' + '"LSACONXB"' + ',' + '"ALTLCNXB"' + ',' +
    '"CAGECDXH"' + ',' + '"REFNUMHA"' + ' '

    The testing code with IN
    WHERE (Static.dbo.intWindow.GuiID = GUIWindowID)

    This is the code part used with a variable
    --AND (Static.dbo.intField.FieldName IN (Criteria))

    That has to coincide with the following codepart
    --AND (Static.dbo.intField.FieldName IN ('LSACONXB','ALTLCNXB','CAGECDXH'))


    Any help appriciated
    Thanks in advance







    Corrie Guest

  3. #3

    Default Re: Formatting string in WHERE Clause

    Thanks Amy


    "Amy" <com> wrote in message
    news:#phx.gbl... 
    a [/ref]
    "CAGECDXH" 
    > ('LSACONXB','ALTLCNXB','CAGECDXH')) 
    >
    >[/ref]


    Karel Guest

Similar Threads

  1. where clause as variable string
    By RichardG in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 28th, 09:54 PM
  2. Formatting String in Datagrid
    By xianxian in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: September 16th, 01:12 PM
  3. Help a newbie! How do I SELECT using a string as a WHERE clause?
    By Pablo Contreras in forum ASP Database
    Replies: 1
    Last Post: July 21st, 05:41 PM
  4. STRING FORMATTING QUESTION
    By Dale Hersh in forum PHP Development
    Replies: 1
    Last Post: July 17th, 06:38 AM
  5. string formatting
    By Michael in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 9th, 02:13 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139