problem in spiliting memo field

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

SQL Server 2000 Forums

Transact-SQL (2000)

problem in spiliting memo field

Author

Topic

namasteall2000
Starting Member

20 Posts

Posted - 2005-05-06 : 16:16:03

Hi,

I have a table having member id and explain column which is a memo field

So table looks like this

Member id Explain
1234 'Adjustment of member # is
05010E12904 Member alrady
enrolled Reversed by Member #
is 05105R000638 Member
Previously Processed
Incorrectly Adjustment Member #
is 05105A000678 Member
Previously Processed Incorrectly'

What I want is to break down this table which will look something like this

Member Id ReversedMemberID AdjustedMemberid
1234 05010E12904 05105R000638
1234 05105A000678

Could any one help me in doing the same.

Thanks

Logicalman
Starting Member

6 Posts

Posted - 2005-05-06 : 16:33:19

Do you want to move all the current data into a new table of the schema you specified, or simply have a run-time function that splits the data up for you on demand ?

Tony

namasteall2000
Starting Member

20 Posts

Posted - 2005-05-06 : 16:35:03

No - a new table will be a good option.

Logicalman
Starting Member

6 Posts

Posted - 2005-05-06 : 17:14:41

Here you go.

This code will take your existing schema and parse out the 'Explain' data into three columns and place it into a temp table.

You can then simply insert all the data from the temp table into your real table.

This code assumes that all the 'Explain' data is in the same format. If not, then you will need to find some similarities between each record and parse using that.

This code could also be altered to allow a larger number of columns, as long as the pattern of 'Explain' is consistant.

Hope this assists,

CREATE PROCEDURE [dbo].[usp_Parse_Explain]

AS
SET NOCOUNT ON

DECLARE @MEMBERID int
DECLARE @EXPLAIN varchar(2000)
DECLARE @CurrMemberID varchar(200)
DECLARE @ReversedMemberID varchar(200)
DECLARE @AdjustedMemberID varchar(200)
DECLARE @NEXT1 int
DECLARE @NEXT2 int

CREATE TABLE #NEWTABLE
( MEMBERID int,
CurrMemberID varchar(50),
ReversedMemberID varchar(50),
AdjustedMemberID varchar(50)
)

SET @MEMBERID = (SELECT TOP 1 [Member id] FROM tblExplain)

WHILE NOT @MEMBERID IS NULL
BEGIN
SET @EXPLAIN = (SELECT TOP 1 Explain FROM tblExplain WHERE [Member id] = @MEMBERID)

SET @NEXT1 = PATINDEX('%# is%', @EXPLAIN)
SET @NEXT2 = PATINDEX('%Member alrady%', @EXPLAIN)
SET @CurrMemberID = LTRIM(RTRIM(SUBSTRING(@EXPLAIN, @NEXT1+5, @NEXT2-(@NEXT1+5))))

SET @EXPLAIN = RIGHT(@EXPLAIN, LEN(@EXPLAIN)-@NEXT2)

SET @NEXT1 = PATINDEX('%# is%', @EXPLAIN)
SET @NEXT2 = PATINDEX('%Member Previously%', @EXPLAIN)
SET @ReversedMemberID = LTRIM(RTRIM(SUBSTRING(@EXPLAIN, @NEXT1+5, @NEXT2-(@NEXT1+5))))

SET @EXPLAIN = RIGHT(@EXPLAIN, LEN(@EXPLAIN)-@NEXT2)

SET @NEXT1 = PATINDEX('%# is%', @EXPLAIN)
SET @NEXT2 = PATINDEX('%Member Previously%', @EXPLAIN)
SET @AdjustedMemberID = LTRIM(RTRIM(SUBSTRING(@EXPLAIN, @NEXT1+5, @NEXT2-(@NEXT1+5))))

INSERT INTO #NEWTABLE
VALUES( @MEMBERID, @CurrMemberID, @ReversedMemberID, @AdjustedMemberID)

SET @MEMBERID = (SELECT TOP 1 [Member id] FROM tblExplain WHERE [Member id] NOT IN (SELECT MEMBERID FROM #NEWTABLE) )

END

SELECT * FROM #NEWTABLE
DROP TABLE #NEWTABLE

SET NOCOUNT OFF

GO

Tony

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-05-06 : 17:44:52

I dont think there is a clean solution to this... you put crap in you get crap out. But heres a go, this should get you started:

declare @explain varchar(1000),
	@phrase varchar(25)
set @explain = 'Adjustment of member # is 05010E12904 Member alrady enrolled Reversed by Member # is 05105R000638 Member Previously Processed Incorrectly Adjustment Member # is 05105A000678 Member Previously Processed Incorrectly' 


set @phrase = 'Adjustment of member # is '
select substring(@explain, patindex('%' + @phrase + '%', @explain) + len(@phrase), (charindex(' Member', @explain, patindex('%' + @phrase + '%', @explain) + len(@phrase))) - (patindex('%' + @phrase + '%', @explain) + len(@phrase)))

set @phrase = 'Reversed by Member # is '
select substring(@explain, patindex('%' + @phrase + '%', @explain) + len(@phrase), (charindex(' Member', @explain, patindex('%' + @phrase + '%', @explain) + len(@phrase))) - (patindex('%' + @phrase + '%', @explain) + len(@phrase)))

set @phrase = 'Adjustment Member # is '
select substring(@explain, patindex('%' + @phrase + '%', @explain) + len(@phrase), (charindex(' Member', @explain, patindex('%' + @phrase + '%', @explain) + len(@phrase))) - (patindex('%' + @phrase + '%', @explain) + len(@phrase)))

namasteall2000
Starting Member

20 Posts

Posted - 2005-05-09 : 13:54:40

Thanks - I will try!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources