Professional Web Applications Themes

CharIndex Problem - Microsoft SQL / MS SQL Server

Charindex should return INT or, if its not found, NULL I canīt figure it out on a stored procedure... select tags = Inserted.Custom16 FROM inserted IF not(CharIndex('<L',tags,1) > 0) select mycar = '<LL>' This actually works IF the Custom16 is not null to beggin with, if custom16 is null, the select mycar never happens! Anyone have a clue?...

Sponsored Links
  1. #1

    Default CharIndex Problem

    Charindex should return INT or, if its not found, NULL

    I canīt figure it out on a stored procedure...


    select tags = Inserted.Custom16 FROM inserted

    IF not(CharIndex('<L',tags,1) > 0)
    select mycar = '<LL>'

    This actually works IF the Custom16 is not null to beggin with, if custom16
    is null, the select mycar never happens!

    Anyone have a clue?


    Sponsored Links
    Guest

  2. #2

    Default Re: CharIndex Problem

    Your problems resides on the fact that charindex returns NULL it either
    search condition or searched string is NULL (since SQL 70).
    So you have NULL value comapring to zero which gives UNKNOWN if SET
    ANSI_NULL is ON. (tried with OFF but still got same result).

    The problems with comapring NULL values is described in SQL BOL: NULL
    Comparison Search Coditions.

    Try this code snipet:

    select tags = Inserted.Custom16 FROM inserted

    IF not(CharIndex('<L',tags,1) > 0) or tags is null
    select mycar = '<LL>'


    --
    Dean Savovic
    www.teched.hr


    <com> wrote in message
    news:phx.gbl... 
    custom16 


    deeeoo Guest

  3. #3

    Default Re: CharIndex Problem

    Thanks... both solutions worked.


    Guest

Similar Threads

  1. contribute problem - access denied file may not existpermission problem
    By Al1973 in forum Macromedia Contribute Connection Administrtion
    Replies: 6
    Last Post: September 17th, 04:16 PM
  2. Replies: 2
    Last Post: September 30th, 12:13 PM
  3. Replies: 0
    Last Post: August 23rd, 11:56 AM
  4. Replies: 0
    Last Post: August 2nd, 03:03 PM
  5. Replies: 2
    Last Post: July 17th, 07:27 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