Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Like Query & Null Values

Like Query & Null Values

Scheduled Pinned Locked Moved Database
databasesql-serversysadminhelpquestion
10 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • R Offline
    R Offline
    rich_wenger
    wrote on last edited by
    #1

    Hi, I have a table on SQL Server where one column allows Null values (docMName) and I'm trying to perform a like query using one parameter. When executed the query fails to include rows with Null values for the docMName column. Is there a way to include these missing rows? Any help would be greatly appreciated. SELECT docIndex, docLName, docFName, docMName FROM tblStaffPhysicians WHERE (docFName + ' ' + docMName + ' ' + docLName LIKE '%' + ? + '%') -- modified at 18:09 Friday 16th September, 2005

    T M 2 Replies Last reply
    0
    • R rich_wenger

      Hi, I have a table on SQL Server where one column allows Null values (docMName) and I'm trying to perform a like query using one parameter. When executed the query fails to include rows with Null values for the docMName column. Is there a way to include these missing rows? Any help would be greatly appreciated. SELECT docIndex, docLName, docFName, docMName FROM tblStaffPhysicians WHERE (docFName + ' ' + docMName + ' ' + docLName LIKE '%' + ? + '%') -- modified at 18:09 Friday 16th September, 2005

      T Offline
      T Offline
      turbochimp
      wrote on last edited by
      #2

      I'm not really sure why you'd create a search like the this intentionally. It's going to wreak havoc on any indexes the execution planner might have taken advantage of. Why not just use separate arguments for the first, middle and last names? SELECT docIndex, docLName, docFName, docMName FROM tblStaffPhysicians WHERE (docFName LIKE '%' + ? + '%') AND ((docMName IS NULL) OR (docMName LIKE '%' + ? + '%')) AND (docLName LIKE '%' + ? + '%') If there is a compelling reason why the statement must use a single parameter value, there are other ways of approaching this, but since you're concatenating a string with spaces right in the SQL statement, it seems like it would be equally sensible to split the parameter value in code on the location(s) of spaces and provide multiple parameters to the command. Hope this helps,

      The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

      R 1 Reply Last reply
      0
      • T turbochimp

        I'm not really sure why you'd create a search like the this intentionally. It's going to wreak havoc on any indexes the execution planner might have taken advantage of. Why not just use separate arguments for the first, middle and last names? SELECT docIndex, docLName, docFName, docMName FROM tblStaffPhysicians WHERE (docFName LIKE '%' + ? + '%') AND ((docMName IS NULL) OR (docMName LIKE '%' + ? + '%')) AND (docLName LIKE '%' + ? + '%') If there is a compelling reason why the statement must use a single parameter value, there are other ways of approaching this, but since you're concatenating a string with spaces right in the SQL statement, it seems like it would be equally sensible to split the parameter value in code on the location(s) of spaces and provide multiple parameters to the command. Hope this helps,

        The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

        R Offline
        R Offline
        rich_wenger
        wrote on last edited by
        #3

        Thank you for responding to my query. When I setup the stored procedure for the application I was unaware that there would be a Null value issue. This is just one view on the data and the concept here was to provide a single textbox to search. I'd rather rewrite the stored procedure than the application if possible. Thanks again.

        R T 2 Replies Last reply
        0
        • R rich_wenger

          Thank you for responding to my query. When I setup the stored procedure for the application I was unaware that there would be a Null value issue. This is just one view on the data and the concept here was to provide a single textbox to search. I'd rather rewrite the stored procedure than the application if possible. Thanks again.

          R Offline
          R Offline
          rich_wenger
          wrote on last edited by
          #4

          This was the reply I got Tom on the Microsoft SQL Forum and it works like a charm. Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com Toms Code================================================= SELECT docIndex, docLName, docFName, docMName FROM tblStaffPhysicians WHERE (docFName + ' ' + ISNULL(docMName, '') + ' ' + docLName LIKE '%' + ? + '%')

          T 1 Reply Last reply
          0
          • R rich_wenger

            Thank you for responding to my query. When I setup the stored procedure for the application I was unaware that there would be a Null value issue. This is just one view on the data and the concept here was to provide a single textbox to search. I'd rather rewrite the stored procedure than the application if possible. Thanks again.

            T Offline
            T Offline
            turbochimp
            wrote on last edited by
            #5

            The example you gave did not look like a stored procedure. It looked like a text SQL command. Stored procedures don't use wildcards (?) as parameter tokens. If you would like to provide the stored procedure source, I will try to help you. I'd prefer to see what you've got versus rewriting something and missing some behavioral nuance.

            The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

            R 1 Reply Last reply
            0
            • R rich_wenger

              This was the reply I got Tom on the Microsoft SQL Forum and it works like a charm. Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com Toms Code================================================= SELECT docIndex, docLName, docFName, docMName FROM tblStaffPhysicians WHERE (docFName + ' ' + ISNULL(docMName, '') + ' ' + docLName LIKE '%' + ? + '%')

              T Offline
              T Offline
              turbochimp
              wrote on last edited by
              #6

              So, if you have a first name and last name, but no middle name in one of your rows, and a user enters text of the form [first name][last name], how exactly is that going to work, since you will be comparing against a concatenated string value of [first name]''[last name]? I would say that the application really does need to change, or you need to make your stored procedure a little smarter about how the comparison string is concatenated (basically, smarter about where to insert spaces). Good luck,

              The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

              1 Reply Last reply
              0
              • R rich_wenger

                Hi, I have a table on SQL Server where one column allows Null values (docMName) and I'm trying to perform a like query using one parameter. When executed the query fails to include rows with Null values for the docMName column. Is there a way to include these missing rows? Any help would be greatly appreciated. SELECT docIndex, docLName, docFName, docMName FROM tblStaffPhysicians WHERE (docFName + ' ' + docMName + ' ' + docLName LIKE '%' + ? + '%') -- modified at 18:09 Friday 16th September, 2005

                M Offline
                M Offline
                Mike Dimmick
                wrote on last edited by
                #7

                The option CONCAT_NULL_YIELDS_NULL controls what happens when you concatenate a NULL with a string. As the name suggests, when you concatenate a NULL with a string and this option is set, the result is NULL. This option is not set by default in the database, but is set by the ODBC driver and OLE DB provider. I'm guessing it's also set by the ADO.NET provider, although I can't find documentation on this. So, when you get a NULL in the docMName column, the result of the concatenation is NULL, hence it doesn't match the query. The change you found on the other forum works because it changes a NULL to an empty string with the ISNULL function. Stability. What an interesting concept. -- Chris Maunder

                R 1 Reply Last reply
                0
                • T turbochimp

                  The example you gave did not look like a stored procedure. It looked like a text SQL command. Stored procedures don't use wildcards (?) as parameter tokens. If you would like to provide the stored procedure source, I will try to help you. I'd prefer to see what you've got versus rewriting something and missing some behavioral nuance.

                  The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

                  R Offline
                  R Offline
                  rich_wenger
                  wrote on last edited by
                  #8

                  Sorry, just got back in town; here is the stored procedure. Looks like you're right, it doesn't pull if I query both a First and Last name. SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE ia.SP_StaffPhysicianSelectCommand ( @Param2 varchar(35) ) AS SET NOCOUNT ON; SELECT docIndex, docLName, docFName, docMName FROM dbo.tblStaffPhysicians WHERE (docFName + ' ' + ISNULL (docMName, '') + ' ' + docLName LIKE '%' + @Param2 + '%') ORDER BY docLName, docFName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -- modified at 20:49 Sunday 18th September, 2005

                  T 1 Reply Last reply
                  0
                  • M Mike Dimmick

                    The option CONCAT_NULL_YIELDS_NULL controls what happens when you concatenate a NULL with a string. As the name suggests, when you concatenate a NULL with a string and this option is set, the result is NULL. This option is not set by default in the database, but is set by the ODBC driver and OLE DB provider. I'm guessing it's also set by the ADO.NET provider, although I can't find documentation on this. So, when you get a NULL in the docMName column, the result of the concatenation is NULL, hence it doesn't match the query. The change you found on the other forum works because it changes a NULL to an empty string with the ISNULL function. Stability. What an interesting concept. -- Chris Maunder

                    R Offline
                    R Offline
                    rich_wenger
                    wrote on last edited by
                    #9

                    Yes, but I'm not out of the woods yet. When I search on a combined string it (John Smith) it fails to return any rows.

                    1 Reply Last reply
                    0
                    • R rich_wenger

                      Sorry, just got back in town; here is the stored procedure. Looks like you're right, it doesn't pull if I query both a First and Last name. SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE ia.SP_StaffPhysicianSelectCommand ( @Param2 varchar(35) ) AS SET NOCOUNT ON; SELECT docIndex, docLName, docFName, docMName FROM dbo.tblStaffPhysicians WHERE (docFName + ' ' + ISNULL (docMName, '') + ' ' + docLName LIKE '%' + @Param2 + '%') ORDER BY docLName, docFName GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -- modified at 20:49 Sunday 18th September, 2005

                      T Offline
                      T Offline
                      turbochimp
                      wrote on last edited by
                      #10

                      Something like the following would work while preserving some benefit from any indices on the table columns and avoiding the double-space problem you'll encounter with the short version you're using now. The procedure below parses the name provided as follows: 1. Everything from the start of the string to the position of the first space is considered the first name. 2. Everything from the position of the first space to the position of the second space is considered the middle name. 3. Everything from the position of the second space to the end of the string is considered the last name. There are obvious issues with even the long version I'm attaching here. For instance, with names that include spaces (e.g. 'Jack Van Horn'). This name will cause problems with either version of the stored procedure; in the version you're using, if no middle name is stored in the database, you'll get the double-space issue, and in my version, 'Van' will get chosen as the middle name, and 'Horn' as the last which will lead to the same conclusion - possibly no returned records. I don't know how critical this search mechanism is to your users, but I really would suggest looking at using a different solution for finding these names. One possibility would be to just do away with using the middle name in the search altogether, and having a new (calculated) column in your table representing First + ' ' + Last names, then just search on that, or do an edit distance algorithm or something. Anyway, here's another way to do it, as promised:

                      CREATE PROCEDURE ia.SP_StaffPhysicianSelectCommand
                      (
                      @Param2 varchar(35)
                      )
                      AS
                      SET NOCOUNT ON;
                      
                      -- Declare local variables
                      declare @firstName nvarchar(35)
                      declare @middleName nvarchar(35)
                      declare @lastName nvarchar(35)
                      declare @count int
                      declare @pos int
                      declare @oldPos int
                      declare @Param2 nvarchar(35)
                      
                      -- Initialize local variables
                      set @oldPos = 0
                      set @count = 0
                      set @Param2 = 'a c'
                      
                      -- Parse the name parameter
                      set @pos = charindex(' ', @Param2)
                      while (@pos > 0)
                      begin
                      	set @count = @count + 1
                      	if (@count <= 2)
                      		begin
                      			if (@count = 1)
                      				-- First name
                      				set @firstName = substring(@Param2, @oldPos, @pos - @oldPos)
                      			else
                      				-- Middle name
                      				set @middleName = substring(@Param2, @oldPos, @pos - @oldPos)
                      		end
                      	else if (@count = 3)
                      		begin
                      	 		-- Last name
                      			set @lastName = right(@Param2, len(@Param2) - @oldPos + 1)
                      			break
                      		end
                      
                      	-- Set a position marker and iterate
                      	set @oldPos = @pos + 1
                      	set @pos = charindex(' ', @Param2
                      
                      1 Reply Last reply
                      0
                      Reply
                      • Reply as topic
                      Log in to reply
                      • Oldest to Newest
                      • Newest to Oldest
                      • Most Votes


                      • Login

                      • Don't have an account? Register

                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • World
                      • Users
                      • Groups