Multiple value extraction, parsing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple value extraction, parsing

Author

Topic

Simonje
Starting Member

5 Posts

Posted - 2012-08-30 : 12:33:26

Hi,

I have some (self-taught) knowledge of SQL, but am currently defeated by a particular problem I am trying to resolve.

I have a 3rd party database that contains a field (called ApprovalString) that stores a document approval route which may contain references to 1 or more approver ID codes in a few slightly different formats. Entries in the field will look like one of the following:

1OF0(^132^) OR 1OF0(^456^) OR 1OF0(^R678^)
1OF0(^H132^)
^132^

The numbers between the ^ symbols represent a unique user ID, however there could be any number of ID's present within the field [Historically we have never had any more than 10 IDs in the field but the software limitations allow significantly more than this].

I am looking to include in a SELECT statement some script that effectively extracts all the user codes from the field, converts them into the users login name (by extracting a field called ExternalRef from a USER table where the ID matches that extracted) and then strings all the user names back into a single field separated by a delimiter, i.e.

For a field that looks like this:

1OF0(^132^) OR 1OF0(^456^) OR 1OF0(^R678^)

I would expect an output of

john.smith,paul.jones,jane.parsons

or for an entry of

^132^

just

john.smith

There is an added complication that some user IDs are preceded by a letter. The letter does not form part of the ID and needs to be removed before the user name can be evaluated.

Any suggestions on how I might achieve this woudl be gratefully received.

Many Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 12:46:43

An example of how you can do what you described below. I may not have followed all the cases you described, but should be close:

CREATE TABLE #users (id VARCHAR(32), lastname VARCHAR(32), firstname VARCHAR(32));
INSERT INTO #users VALUES
('132','smith','john'),
('456','jones','paul'),
('R678','parsons','jane');

CREATE TABLE #Approvals(ApprovalId INT, AprovalString VARCHAR(255));
INSERT INTO #Approvals VALUES
(1,'1OF0(^132^) OR 1OF0(^456^) OR 1OF0(^R678^)'),
(2,'1OF0(^H132^)'),
(3,'^132^');


SELECT
	a.ApprovalId,
	b.firstname + '.' + b.lastname
FROM
	#Approvals a
	INNER JOIN #users b ON 
		a.AprovalString LIKE  '%^'+b.Id+'^%'
		OR a.AprovalString LIKE '%^[a-zA-Z]'+b.id+'^%'
ORDER BY
	a.ApprovalId;
	
DROP TABLE #users;
DROP TABLE #Approvals;

Simonje
Starting Member

5 Posts

Posted - 2012-08-30 : 13:07:29

Thanks Sunita,

Very clever; However all the outputs are show as separate rows. How do I concatenate the entries in respect of the same ApprovalID into a single delimited string?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 15:25:13

I made the original query into a CTE to concatenate. It possibly could be done without the subquery, but this is the path of least change :)

CREATE TABLE #users (id VARCHAR(32), lastname VARCHAR(32), firstname VARCHAR(32));
INSERT INTO #users VALUES
('132','smith','john'),
('456','jones','paul'),
('R678','parsons','jane');

CREATE TABLE #Approvals(ApprovalId INT, AprovalString VARCHAR(255));
INSERT INTO #Approvals VALUES
(1,'1OF0(^132^) OR 1OF0(^456^) OR 1OF0(^R678^)'),
(2,'1OF0(^H132^)'),
(3,'^132^');


;WITH cte AS
(	
	SELECT
		a.ApprovalId,
		b.firstname + '.' + b.lastname AS FnameLName
	FROM
		#Approvals a
		INNER JOIN #users b ON 
			a.AprovalString LIKE  '%^'+b.Id+'^%'
			OR a.AprovalString LIKE '%^[a-zA-Z]'+b.id+'^%'
)
SELECT
	a.ApprovalId,
	STUFF(b.FnameLName,1,1,'') AS FnameLName
FROM
	#Approvals a
	CROSS APPLY
	(
		SELECT ','+ c.FnameLName AS [text()]
		FROM cte c
		WHERE c.ApprovalId = a.ApprovalId
		FOR XML PATH('')
	) b(FnameLName)
ORDER BY
	a.ApprovalId;
	
	
DROP TABLE #users;
DROP TABLE #Approvals;

Simonje
Starting Member

5 Posts

Posted - 2012-09-01 : 17:02:29

Thanks again Sunita.

This works brilliantly when I test this in a SQL 2008 environment, however the database I need to apply this functionality to is only SQL 2000 (The application that uses this db is not 2005 or 2008 compliant so there is no hope of migrating). Is there any way of achieving the same result in SQL 2000 complaint code?

Many Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-02 : 12:14:07

see table valued udf method here

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources