Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-06
8
Medium Priority
?
784 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
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are some very powerful Dynamic 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 di…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

885 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