Solved

SQL 2005 Search for Multiple substrings within a single column w/o fixed position

Posted on 2009-04-06
8
769 Views
Last Modified: 2012-05-06
Help, I have one column that contains user information and I have been trying to figure out how to search for multiple substrings and if there is a match pass that exact match to another column.

For example:

Employees
___________
Doe, John R Civilian TN
Doe, Jane Civilian PA
Doe, Tim S Contractor NY
Doe, Sean Contractor KY

The problem i have is that I can count on there being exactly one space between characters and there may or may not be a middle initial. I looked at CHARINDEL and PATINDEX but can't figure it out to pull this off. Once I know how to do this I was going to try and set it up for ADSi but that is for another day. Is anyone willing to lend a hand?
0
Comment
Question by:NEARNG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24079113
For the sample values above, what exactly do you need extracted or to be the result?
0
 
LVL 8

Expert Comment

by:vinurajr
ID: 24079255
see the code... what exactly u are going to achieve from charindex and patindex...?
declare @TestTable table(sno int identity,nam varchar(max))
insert into @TestTable
select 'Doe, John R Civilian TN'
union all
select 'Doe, Jane Civilian PA'
union all
select 'Doe, Tim S Contractor NY'
union all
select 'Doe, Sean Contractor KY'
 
select patindex('% %',nam) from @TestTable
select charindex(' ',nam) from @TestTable

Open in new window

0
 

Author Comment

by:NEARNG
ID: 24080385
I was looking to pull Contractor and Civilian from the sample. There are several others but with two i would be able to see what was needed for the rest.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:NEARNG
ID: 24080417
In general terms:

Search within "Doe, John R Civilian TN" for "Contractor" or "Civilian" in any position and return it as othercolumnname so that I would have the original one and one with just the employee type.

Thanks for looking at this.
0
 
LVL 25

Expert Comment

by:reb73
ID: 24082927
If all the values end with the two character state/province code, then the code below will extract the employeetype as a new column -



SELECT EmployeeType = REVERSE(LEFT(REVERSE(LEFT(Employees,LEN(Employees)-3)), CHARINDEX(' ',REVERSE(LEFT(Employees,LEN(Employees)-3)))-1))
FROM YourTable

Open in new window

0
 

Author Comment

by:NEARNG
ID: 24087565
Your solution would work great and I originally used something similar until I found that sometimes there were extra spaces or someone went outside of the naming convention and added extra characters to the state value. The record set is being pulled from Active Directory via ADSi so you can see where the potential for many variants exists vs. a well structured DB. Is it possible within SQL to just seach for the first occurance of a condition from the right rather than take into account the state value. Something like the AutoIt script function StringInStr? If not, the points are all yours, I like your solution better than the one that I wrote. Thanks again for looking into this for me.

AutoIt Script
________________-

StringInStr ( "string", "substring" [, casesense [, occurrence [, start [, count]]]] )

string = The string to evaluate.

substring = The substring to search for.

casesense = [optional] Flag to indicate if the operations should be case sensitive.

occurrence = [optional] Which occurrence of the substring to find in the string. Use a negative
occurrence to search from the right side. The default value is 1 (finds first occurrence).

start = [optional] The starting position of the search.

count = [optional] The number of comparisons to make.

0
 
LVL 25

Accepted Solution

by:
reb73 earned 125 total points
ID: 24088175
"Is it possible within SQL to just seach for the first occurance of a condition from the right rather than take into account the state value"

Yes, but using a combination of REVERSE and CHARINDEX (for conditions matching a single character)  or REVERSE AND PATINDEX(for conditions matching a string pattern)

I've slightly changed my code to search based on the last occurence of a space as the condition instead of looking for a two character state code below -

You could probably write a user defined function (UDF) to mimic the AutoIt script function StringInStr and then use this UDF against the column which may be slightly slower but much more cleaner. I'll see if I can try and code a UDF for you in the next couple of days based on your last post, but it may be better to post it as a separate question to obtain a quicker response from other experts (preferably link this as a related question).
SELECT	EmployeeType = REVERSE(LEFT(REVERSE(LEFT(Employees,LEN(Employees)-CHARINDEX(' ',REVERSE(Employees)))),CHARINDEX(' ',REVERSE(LEFT(Employees,LEN(Employees)-CHARINDEX(' ',REVERSE(Employees)))))-1))
FROM	yourtable

Open in new window

0
 

Author Closing Comment

by:NEARNG
ID: 31567612
reb73,
Thank you for taking the time to sort this out. If you can create the function would you be so kind as to comment it as if you were educating a 1st grader. After 4 course on SQL I still don't use it enough to retain it so I comment everything so that when I go back to it or if I neeed to modify it I understood what I did way back when. I will wait a few days and post as you suggested. Thanks again for the excelent assistance.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question