How can I use LIKE in a SQL query to find any string containing a space when CHARINDEX is conflicting with it?

So I have a field which I'm filtering with in a query.  I'm using the LIKE keyword which is intended to find any string containing at least one space (this field holds full names -- no, it's not a normalized table).  The problem is this: when I use '% %' for the LIKE condition I get the following error: "Invalid length parameter passed to the SUBSTRING function."  After goggling a bit, I found that there may be an incompatibility between CHARINDEX and LIKE (I'm using both...I have CHARINDEX finding spaces for me).  Note also, that PATINDEX apparently has the same issue.  I'd like to not use any WHERE clause at all, but I get the same error if I do that.  A LIKE condition that does not get an error is this one that looks for a space and something else (ex. '[a-z] %'...note the space...but this is too restrictive for my purposes).


CREATE TABLE #tblPlanNames (planned_officiant_name varChar(100), planned_officiant_LastName varChar(100))
BEGIN
  INSERT INTO #tblPlanNames 
  SELECT planned_officiant_name, ltrim(rtrim(replace(replace(replace(replace(planned_officiant_name,'  ',' '),'  ',' '),'801) ','801)'),'435) ','435)'))) planned_officiant_LastName FROM marriage WHERE planned_officiant_name is not NULL
END

SELECT DISTINCT  
CASE 
when charindex(' ', planned_officiant_LastName) >  0 AND patindex('%[0-9]%', reverse(left(reverse(planned_officiant_LastName), charindex(' ', reverse(planned_officiant_LastName))-1))) = 0 then reverse(left(reverse(planned_officiant_LastName), charindex(' ', reverse(planned_officiant_LastName))-1)) 
when charindex(' ', planned_officiant_LastName) >  0 AND patindex('%[0-9]%', reverse(left(reverse(planned_officiant_LastName), charindex(' ', reverse(planned_officiant_LastName))-1))) > 0 then reverse(left(right(reverse(planned_officiant_LastName), len(planned_officiant_LastName) - charindex(' ', reverse(planned_officiant_LastName))), charindex(' ', right(reverse(planned_officiant_LastName), len(planned_officiant_LastName) - charindex(' ', reverse(planned_officiant_LastName)))) - 1))
ELSE planned_officiant_LastName  
END lastName, planned_officiant_name 
FROM #tblPlanNames 
WHERE planned_officiant_LastName is not NULL AND ltrim(rtrim(planned_officiant_LastName)) <> '' 
  AND planned_officiant_LastName LIKE '% %') 
ORDER BY lastName

DROP TABLE #tblPlanNames

Open in new window


Thoughts?
LVL 15
David L. HansenCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
Can you post some sample data from your table and the expected result
Kevin CrossChief Technology OfficerCommented:
Try: WHERE LTRIM(RTRIM(planned_officiant_LastName)) LIKE '% %'
Or: WHERE planned_officiant_LastName LIKE '[^ ]%[ ]%[^ ]'

One issue could be that you are matching on ' ' at the beginning or at the end.  Another solution is to use the CHARINDEX(' ', ...) > 0 in the WHERE also.
David L. HansenCEOAuthor Commented:
Sure.

id     |     planned_officiant_name     |     clerk
10    |         John Doe 867-5309        |        Clerk A
11    |         Judge Doe 777-0001      |        Clerk B
12    |         Thomas Payne               |        ClerkA

Note that the name often has a phone number in-line with it (same string) but not always...again, this table is not at all normalized --legacy stuff).  I'm trying to get just the last name out of the string (out of planned_officiant_name).  My patindex/charindex stuff (see the case statement) is written for the sole purpose of getting just the last name.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Kevin CrossChief Technology OfficerCommented:
Or flip the wildcards to the outside, then you will match anything (even records with spaces on the outside since you have LTRIM/RTRIM for use later).

SELECT CASE when charindex(' ', planned_officiant_LastName) >  0 AND patindex('%[0-9]%', reverse(left(reverse(planned_officiant_LastName), charindex(' ', reverse(planned_officiant_LastName))-1))) = 0 then reverse(left(reverse(planned_officiant_LastName), charindex(' ', reverse(planned_officiant_LastName))-1)) 
when charindex(' ', planned_officiant_LastName) >  0 AND patindex('%[0-9]%', reverse(left(reverse(planned_officiant_LastName), charindex(' ', reverse(planned_officiant_LastName))-1))) > 0 then reverse(left(right(reverse(planned_officiant_LastName), len(planned_officiant_LastName) - charindex(' ', reverse(planned_officiant_LastName))), charindex(' ', right(reverse(planned_officiant_LastName), len(planned_officiant_LastName) - charindex(' ', reverse(planned_officiant_LastName)))) - 1))
ELSE planned_officiant_LastName  
END lastName, planned_officiant_lastname
FROM (
    VALUES('John Doe 867-5309'),('Judge Doe 777-0001'),('Thomas Payne'),(' Not_Valid ')
) t(planned_officiant_LastName)
WHERE planned_officiant_LastName LIKE '%[^ ][ ][^ ]%'
;

Open in new window

Surendra NathTechnology LeadCommented:
The issue is coming from your Left Function or the right function, these internally uses the substring function, hence they are throwing the error as substring error.

Check the lenght's in the LEFT and RIGHT that you have provided, in the select statement and see if they are negative.

If they are negative or NULL you will get this error, correct it out and it should be sorted.
awking00Information Technology SpecialistCommented:
There two statements are precisely the same thing -

when charindex(' ', planned_officiant_LastName) >  0

where ... planned_officiant_LastName LIKE '% %'
David L. HansenCEOAuthor Commented:
Kevin,
Try: WHERE LTRIM(RTRIM(planned_officiant_LastName)) LIKE '% %'
Or: WHERE planned_officiant_LastName LIKE '[^ ]%[ ]%[^ ]'
gives the exact same error (as stated).

Neo_jarvis,
Shouldn't the ELSE case take care of any negatives or nulls occurring in the first two cases?
David L. HansenCEOAuthor Commented:
awking00,

I see what you mean logically, but I'm not sure what solution you are pointing to.
Surendra NathTechnology LeadCommented:
No, Not neccssarily

Because let us say the string has a space within it


when charindex(' ', planned_officiant_LastName) >  0 AND patindex('%[0-9]%', reverse(left(reverse(planned_officiant_LastName), charindex(' ', reverse(planned_officiant_LastName))-1))) > 0 then reverse(left(right(reverse(planned_officiant_LastName), len(planned_officiant_LastName) - charindex(' ', reverse(planned_officiant_LastName))), charindex(' ', right(reverse(planned_officiant_LastName), len(planned_officiant_LastName) - charindex(' ', reverse(planned_officiant_LastName)))) - 1))


in the above statement, if the right function already eliminates the spaces, then the left function will have nothing to eliminate right... So in that place the charindex will return zero and the charindex - 1 will result in a length of -1 ( essentially you are doing left(X,-1) _ which is an error...
Kevin CrossChief Technology OfficerCommented:
Exactly as Neo_jarvis said.
e.g., 'Judge 777-0001'
Kevin CrossChief Technology OfficerCommented:
Something like this could work:

SELECT SUBSTRING(planned_officiant_lastname, CHARINDEX(' ', planned_officiant_LastName)+1, CHARINDEX(' ', planned_officiant_LastName+' ', CHARINDEX(' ', planned_officiant_LastName)+1)-CHARINDEX(' ', planned_officiant_LastName))
FROM (
    VALUES('John Doe 867-5309'),('Judge 777-0001'),('Thomas Payne'),(' Not_Valid ')
) t(planned_officiant_LastName)
WHERE planned_officiant_LastName LIKE '%[^ ][ ][a-z]%'
;

Open in new window


Note the updated LIKE grabs values with a letter after the space.  If you want to include records with phone numbers also, you can add that to a CASE statement to show last name or NULL.  Then you can create a different column to show the phone number.
David L. HansenCEOAuthor Commented:
I see...yes.  Not sure how to fix it though, perhaps putting another WHEN in between the existing two and test for a "< 0" scenario??
David L. HansenCEOAuthor Commented:
I just added a third WHEN (and I placed it in between the original two)...here it is:

when patindex('% %', planned_officiant_LastName) >  0 AND patindex('%[0-9]%', reverse(left(reverse(planned_officiant_LastName), patindex('% %', reverse(planned_officiant_LastName))-1))) < 0 then planned_officiant_LastName

Guess what though?  After adding this and changing the LIKE to use '% %' I still get the same error.  :-(
Kevin CrossChief Technology OfficerCommented:
Yes, it will not work simply to add a WHEN (at least not the one you think).  The challenge is that the scenario meets the criteria of having a number pattern.  The problem is when you take off the number, there is only one name left (i.e., no space): 'Judge 777-0001'.

Did you not like the way I showed here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28326433.html#a39742525
David L. HansenCEOAuthor Commented:
Sorry Kevin, I totally missed that one.  So when I use that solution it looks like this:
--Handle poor data
CREATE TABLE #tblPlanNames (planned_officiant_name varChar(100), planned_officiant_LastName varChar(100))
BEGIN
  INSERT INTO #tblPlanNames 
  SELECT planned_officiant_name, ltrim(rtrim(replace(replace(replace(replace(planned_officiant_name,'  ',' '),'  ',' '),'801) ','801)'),'435) ','435)'))) planned_officiant_LastName FROM marriage WHERE planned_officiant_name is not NULL
END

SELECT DISTINCT SUBSTRING(planned_officiant_lastname, CHARINDEX(' ', planned_officiant_LastName)+1, CHARINDEX(' ', planned_officiant_LastName+' ', CHARINDEX(' ', planned_officiant_LastName)+1)-CHARINDEX(' ', planned_officiant_LastName)) lastName 
, planned_officiant_name 
from #tblPlanNames 
where planned_officiant_LastName is not NULL AND ltrim(rtrim(planned_officiant_LastName)) <> '' 
  --AND planned_officiant_LastName like '% %'order by lastName
DROP TABLE #tblPlanNames

Open in new window

and it seems to be working (I don't even have to use a LIKE anymore -- nice)...however, for something like "(B ) Lindsay Jones" instead of returning "Jones" it returns ")".  I need to better understand your condition.

The logic I'm after is this:   Return the whole name if no spaces exist in the name field, although if the name contains at least one space and the last word in that string contains numerics, then return the second-to-the-last word (whatever that might be).  Finally, if a space exists and the last word does not contain numerics then return that last word.
Kevin CrossChief Technology OfficerCommented:
It seems like you need something like I have here '%[^ ][ ][a-z]%' that determines that the space is before another word versus a special character or number.  You could then put this in a CASE that matches your intent.

CASE
  WHEN planned_officiant_LastName LIKE '%[^ ][ ][a-z]%' THEN ...
  WHEN planned_officiant_LastName LIKE '%[^ ][ ][^a-z]%' THEN ...
  ELSE planned_officiant_LastName
END

Although, in writing this out, I think using PATINDEX instead of CHARINDEX or LIKE may be better for you as what you are after is finding where you have two words connected by a space versus just the existence of a space.
David L. HansenCEOAuthor Commented:
Also, "(B) Adam Doe 111-302-7758" returns "Adam" instead of "Doe"....I think that's because we got rid of the Reverses.  Your solution returns the second word going from left-to-right...I am so sorry....when I say I need the second word (because a phone number is at the end) I mean the second word going from right-to-left.   Last Name is what I need.
David L. HansenCEOAuthor Commented:
Just wanted to say thanks in advance of the final solution....this is very helpful!
Surendra NathTechnology LeadCommented:
can you post some data for the table planNames here preferably insert statements, so that we can test it out...

We need one for each of the cases above...
David L. HansenCEOAuthor Commented:
Ok:

('John Doe 867-5309'), ('Judge 777-0001'), ('Thomas Payne'), ('(Bishop) John D. Doe (111)222-4444'), ('(B) John Ethan Doe 111-444-5555')
David L. HansenCEOAuthor Commented:
I guess ('Doe') exists too and should just return 'Doe'
Kevin CrossChief Technology OfficerCommented:
Given the cases above, this seems to work.  Note: I used a common table expression (you can use a derived table if you cannot use CTEs) to simplify the final expression as it eliminates the need to repeat the split of numbers, prefixes, et cetera.

WITH cte AS (
SELECT planned_officiant_LastName
     , parsed_officiant_LastName = 
           SUBSTRING(planned_officiant_LastName, 
                     PATINDEX('%[ ][a-z]%', ' '+planned_officiant_LastName), 
                     LEN(planned_officiant_LastName) -
                         PATINDEX('%[ ][a-z]%', ' '+planned_officiant_LastName) -
                         PATINDEX('%[ ][a-z]%', ' '+REVERSE(planned_officiant_LastName)) +2)
FROM (
    VALUES('John Doe 867-5309'), 
          ('Judge 777-0001'), 
          ('Thomas Payne'), 
          ('(Bishop) John D. Doe (111)222-4444'), 
          ('(B) John Ethan Doe 111-444-5555'), 
          ('Doe')
) tblPlanNames(planned_officiant_LastName)
)
SELECT lastName = RIGHT(parsed_officiant_LastName, CHARINDEX(' ', REVERSE(parsed_officiant_LastName)+' ')-1)
FROM cte
;

Open in new window

Scott PletcherSenior DBACommented:
Please try this instead:

SELECT DISTINCT ca2.LastName
FROM (
    VALUES('John Doe 867-5309'),('Judge Doe 777-0001'),('Thomas Payne'),(' Not_Valid ')
) t(planned_officiant_LastName)
CROSS APPLY (
    SELECT RTRIM(LTRIM(CASE WHEN PATINDEX('%[0-9]%', t.planned_officiant_LastName) > 0
        THEN LEFT(t.planned_officiant_LastName, PATINDEX('%[0-9]%', t.planned_officiant_LastName) - 1)
        ELSE t.planned_officiant_LastName END)) AS LastName_Minus_Numbers
) AS ca1
CROSS APPLY (
    SELECT LTRIM(RTRIM(RIGHT(ca1.LastName_Minus_Numbers, CHARINDEX(' ', REVERSE(' ' + ca1.LastName_Minus_Numbers))))) AS LastName
) AS ca2
WHERE
    planned_officiant_LastName > ''
--ORDER BY LastName

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David L. HansenCEOAuthor Commented:
That did it...Thanks guys...appreciate it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.