Parse and Merge two fields

JR

unread,

Oct 15, 2007, 12:25:46 PM10/15/07

to

I have kind of a complex situation and wondering if this could be
scripted:

Contains two columns of data, "field1" and "field2" Sample data:

So you can see a field1 contains multiple names, and Field2 contains
their job titles. Both are separated by the "|" key. I want to merge
these differently into Field3:

Field3 would look like:

So it would based on the pipes, merge the name and title. It would
populate each row in the database based on what's in it's associated
Field1 and Field2 values.

Any help would be appreciated as this is a hot item I need to take
care of.

Thanks.

JR

Carl Henthorn

unread,

Oct 15, 2007, 1:21:00 PM10/15/07

to

I cant find my example code right now, but what you need to do is first count
the number of delimiters in the string and set that to a variable. then loop
through the string that many times, substring'ing out the value, and
replacing that value with an empty string so you dont grab it again. set the
substring to a variable. then do the same for the second string, and cat them
together into another variable or a table. As long as there are always the
same number of values in both strings, just loop through both strings in the
same structure and it will be easy!

JR

unread,

Oct 15, 2007, 6:04:43 PM10/15/07

to

On Oct 15, 12:21 pm, Carl Henthorn

> > JR- Hide quoted text -
>
> - Show quoted text -

Thanks for the input Carl. I am working on this now. If you or
anyone has any samples, let me know.

Where I am so far:

DECLARE @txt VARCHAR(100)
SET @txt ='|'

SELECT employees,
(LEN(employees)-LEN(REPLACE(employees,@txt,'')))/LEN(@txt)
FROM people

Probably need to add +1 to this as it gives me one less than I need
however a start. This will get me the number of occurrences.

Thanks.

JR

unread,

Oct 15, 2007, 8:11:40 PM10/15/07

to

Here's what I got. What ya think? Just need to update it back to the
record instead of print statements using the "id" value as the unique
value

use CAMPO_NOTES
DEALLOCATE tnames_cursor
DECLARE @people varchar(1000)
DECLARE @position varchar(1000)
DECLARE @id varchar(20)
DECLARE @valuess int

DECLARE @txt VARCHAR(100)
SET @txt = '|>'

DECLARE tnames_cursor CURSOR

FOR
SELECT id,staff,posizione, ((LEN(staff)-
LEN(REPLACE(staff,@txt,'')))/LEN(@txt)) as valuess
FROM EXP
OPEN tnames_cursor

FETCH NEXT FROM tnames_cursor INTO @id,@people,@position,@valuess
WHILE (@@FETCH_STATUS <> -1)
BEGIN
print @valuess
if (@valuess > 0)
BEGIN
WHILE (@valuess > 0)
BEGIN
print LEFT(@people, CHARINDEX('|>', @people) - 1)
SET @people = right(@people, len(@people) - CHARINDEX('|>',
@people) - 1)
print LEFT(@position, CHARINDEX('|>', @position) - 1)
SET @position = right(@position, len(@position) - CHARINDEX('|
>', @position) - 1)
SET @valuess = @valuess - 1
END
END
ELSE
BEGIN
print @people
print @position
END
print '----------------------------------------------------'
FETCH NEXT FROM tnames_cursor INTO @id,@people,@position,@valuess
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

Bob

unread,

Oct 15, 2007, 10:18:01 PM10/15/07

to

Ok, here's an example for you using a WHILE loop. It works with your sample
data, I've added two other rows and a pair_id - you'll need something similar
which relates the two rows in your data, ie in a multi-row table, how does
row1 know it relates to row2?
It currently hard-typed for 4 items per row, I'll leave it to you to figure
out a neater exit! Oh and it will fall over if your rows have differing
numbers of items. Good luck!

--DROP TABLE #piped_values
GO
CREATE TABLE #piped_values( pair_id INT, field_name INT, initial_value
VARCHAR(80) ) --
GO

--DROP TABLE #concat_values
GO
CREATE TABLE #concat_values( pair_id INT PRIMARY KEY, field_name INT, pos1
INT, pos2 INT, concat_value VARCHAR(400) )
GO

SET NOCOUNT ON

GO

-- Add first name value
INSERT INTO #concat_values ( pair_id, field_name, pos1, pos2, concat_value )
SELECT pair_id, field_name, CHARINDEX( '|', initial_value ), 1, LEFT(
initial_value, CHARINDEX( '|', initial_value ) )
FROM #piped_values
WHERE field_name = 1
GO

DECLARE @i INT

SET @i = 1

WHILE @i < 8
BEGIN

-- Flick between adding title or name
IF @i % 2 = 1
BEGIN

-- Add next title value
UPDATE cv
SET concat_value = concat_value + SUBSTRING( initial_value, cv.pos2,
ISNULL( NULLIF( CHARINDEX( '|', initial_value , cv.pos2 + 1 ), 0 ) - cv.pos2,
LEN( initial_value ) ) ),
pos2 = CHARINDEX( '|', initial_value, cv.pos2 + 1 )
FROM #piped_values pv
INNER JOIN #concat_values cv ON pv.pair_id = cv.pair_id
WHERE pv.field_name = 2

END
ELSE
BEGIN

-- Add next name value
UPDATE cv
SET concat_value = concat_value + SUBSTRING( initial_value, cv.pos1,
ISNULL( NULLIF( CHARINDEX( '|', initial_value, cv.pos1 + 1 ), 0 ) - cv.pos1,
LEN( initial_value ) ) ),
pos1 = CHARINDEX( '|', initial_value, cv.pos1 + 1 )
FROM #piped_values pv
INNER JOIN #concat_values cv ON pv.pair_id = cv.pair_id
WHERE pv.field_name = 1

END

SET @i = @i + 1

-- Exit loop if there's no more work to do
-- !!TODO - one for the user to do!!
END

SELECT 'Finish', @i, * FROM #concat_values
GO
SET NOCOUNT OFF

Steve Dassin

unread,

Oct 17, 2007, 4:39:03 AM10/17/07

to

Here is the way this problem should be solved in a database:

http://beyondsql.blogspot.com/2007/10/dataphor-merging-strings-together.html

best,
steve
www.beyondsql.blogspot.com