Professional Web Applications Themes

execute a string as a logical expression - Microsoft SQL / MS SQL Server

Using SQL Server 2000. I have a table of logical tests in the form of strings ---------------------------------------------------------- create table tests ( test nvarchar(255) not null primary key ) go insert into tests (test) values ('(0.5 <= 1.0)') insert into tests (test) values ('(2 = 3)') insert into tests (test) values ('((2 < 3) AND (4 < 5))') ----------------------------------------------------------- I'd like to return a result showing the test and the logical result of the test test result ----------------------------------- ------ ((2 < 3) AND (4 < 5)) true (0.5 <= 1.0) true (2 = 3) false I've struggled with this for ages ...

  1. #1

    Default execute a string as a logical expression

    Using SQL Server 2000.

    I have a table of logical tests in the form of strings

    ----------------------------------------------------------
    create table tests
    (
    test nvarchar(255) not null primary key
    )
    go

    insert into tests (test) values ('(0.5 <= 1.0)')
    insert into tests (test) values ('(2 = 3)')
    insert into tests (test) values ('((2 < 3) AND (4 < 5))')
    -----------------------------------------------------------

    I'd like to return a result showing the test and the logical result of the
    test

    test result
    ----------------------------------- ------
    ((2 < 3) AND (4 < 5)) true
    (0.5 <= 1.0) true
    (2 = 3) false


    I've struggled with this for ages and come up with nothing. I'm open to
    using cursors if that's what it takes - any help would be greatly
    appreciated.


    John Guest

  2. #2

    Default Re: execute a string as a logical expression

    "John Winterbottom" <com> wrote in message
    news:bidfts$7uhr0$news.uni-berlin.de... 



    Sorry to all - I found a solution almost immediately after posting. I only
    started thinking about cursors after I posted and of course that makes it so
    much easier :) This is what I have:

    declare s nvarchar(255)

    declare c cursor for
    select test
    from tests

    open c
    fetch next from c into s
    while (fetch_status=0)
    begin
    set s = 'SELECT CASE WHEN ' + s + ' THEN 1 ELSE 0 END'
    exec (s)
    fetch next from c into s
    end
    close c
    deallocate c
























    John Guest

  3. #3

    Default Re: execute a string as a logical expression

    Why in SQL? Use a spreadsheet or do it in your client application.

    If you must, then the following works provided the total length of your
    expressions doesn't take it over 8000 chars and assuming your results only
    evaluate to TRUE or FALSE (not UNKNOWN):

    DECLARE sql VARCHAR(8000)

    SELECT sql = COALESCE(sql + ' UNION ALL ','')
    + 'SELECT '
    + ''''+test+''','
    + 'CASE WHEN'
    + test
    + ' THEN ''TRUE'' ELSE ''FALSE'' END'
    FROM Tests

    EXEC (sql)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  4. #4

    Default Re: execute a string as a logical expression

    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Thanks for the reply David. The logical values are themselves generated from
    other calculated results in the database - doing it on the client would
    result in a huge amount of extra round-trips. Short of splitting this out
    into a separate tier I'm pretty much stuck with keeping it on the server.

    All of the tests "should" evaluate to only either true or false, but I'll
    include error handling for any that don't. Thanks for the help.

















    John Guest

  5. #5

    Default Re: execute a string as a logical expression

    Hi John,

    Here's a solution using the undoented system
    procedure xp_execresultset:


    exec master..xp_execresultset
    'select sql from (
    select ''
    declare t table (
    test nvarchar(255),
    result varchar(5)
    )
    declare false varchar(5) set false = ''''false''''
    declare true varchar(5) set true = ''''true'''''' sql,
    1 pos
    union all
    select ''insert into t values
    ('''''' + test + '''''', case when '' + test + '' then ''''true''''
    else ''''false'''' end)'',
    2
    from tests
    union all
    select ''select test, result from t'', 3
    ) T
    order by pos', N'tempdb'

    -- Steve Kass
    -- Drew University
    -- Ref: 0D461AC4-7B90-48A9-A07A-D0EE3B0DCFD0

    John Winterbottom wrote:
     

    Steve Guest

  6. #6

    Default Re: execute a string as a logical expression

    Oops - be sure to use the database your table is in where I
    have N'tempdb'

    SK

    John Winterbottom wrote:
     

    Steve Guest

Similar Threads

  1. execute string
    By ChronoFish in forum PHP Development
    Replies: 2
    Last Post: January 14th, 05:20 PM
  2. #26262 [Opn->Bgs]: Error in evaluate logical expression
    By iliaa@php.net in forum PHP Development
    Replies: 0
    Last Post: November 15th, 05:56 PM
  3. #26262 [NEW]: Error in evaluate logical expression
    By updates at mail dot ru in forum PHP Development
    Replies: 0
    Last Post: November 15th, 02:35 PM
  4. Replies: 0
    Last Post: October 13th, 11:11 AM
  5. #25849 [NEW]: regular expression on a UTF-8 string brakes this string
    By troublegum at woltlab dot de in forum PHP Development
    Replies: 0
    Last Post: October 13th, 11:02 AM

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