Solved

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

Posted on 2009-04-06
8
737 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now