Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

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

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
NEARNG
Asked:
NEARNG
  • 4
  • 3
1 Solution
 
reb73Commented:
For the sample values above, what exactly do you need extracted or to be the result?
0
 
vinurajrCommented:
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
 
NEARNGAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
NEARNGAuthor Commented:
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
 
reb73Commented:
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
 
NEARNGAuthor Commented:
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
 
reb73Commented:
"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
 
NEARNGAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now