Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-06
8
Medium Priority
?
779 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore 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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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