Parsing Data By "\" and "/" Characters

Question

Post reply

Parsing Data By "\" and "/" Characters

Sean Brown

SSC-Addicted

Points: 449
More actions
May 18, 2005 at 4:12 pm

#362599

Brand new CDI programmer here.

I receive a data field from a file that looks something like this

x10\210\63\1\10\J-type\4/...

I need tp parse the values between the \'s to their own fields. Everything after the / can be ignored. So the example data would end up like:

Field 1 = x10, Field 2 = 210, Field 3 = 63 , etc.

*the data between the /'s is not necessarily the same length ot type for each record received, but there is a set number of \. I will be parsing to a total of 7 fields

Whoever wrote the process didn’t really do all that great of a job, and I have been tasked with fixing it. The only way that I can think of to do it would be something like using charindex or patindex to find the substring of everything before the /, then update the original field with that substring. The take get the substring of everything up til the first \, put that in the receiving field, then take the substring of everything after the first \, and update the original field with that. Wash, rinse, repeat for each set. Unfortunately, that doesn’t seem like it would be much better than what we have now. Anybody have any ideas of how to do it more efficiently?

Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

There's a generic parsing script in the scripts archive on this site that could be modified to fit your need.

http://qa.sqlservercentral.com/scripts/contributions/1445.asp

If you have the option of bringing this data in through DTS, you could also do the parsing there and just end up with the data you desire in a table structure of your choice.

And then again, I might be wrong ...
David Webb

Lowell SSC Guru Points: 323482 More actions · Answer 2

you could also use the SPLIT function off of this web site; it returns a @table of data,; for example

select * from dbo.split('x10\210\63\1\10\J-type\4/...','\')

returns:

strval ------

x10

210

63

1

10

J-type

4/...

i would imagine from there you could use each row as appropriate, and maybe split the last field on a right slash to get the table

select * from dbo.split('4/...','/')

strval ------

4

...

for reference, here is the SPLIT function from this web site:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPLIT]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[SPLIT]

GO

CREATE FUNCTION SPLIT (

@str_in VARCHAR(8000),

@separator VARCHAR(4 )= ',' )

RETURNS @strtable TABLE (strval VARCHAR(8000))

AS

BEGIN

DECLARE

@Occurrences INT,

@Counter INT,

@tmpStr VARCHAR(8000)

SET @Counter = 0

IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator

SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences

BEGIN

SET @Counter = @Counter + 1

INSERT INTO @strtable

VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0

BREAK

END

RETURN

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Brown SSC-Addicted Points: 449 More actions · Answer 3

Thanks, those are good places to start. Unfortunatly, I can' t use DTS. Our policy is to DTS the files into a

staging table that is an exact copy of the raw data file. So I am actually working with the staging table,

moving through a template table into production. Sorry I didn't mention this. Thanks for the ideas.

--Sean

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Here is yet another example. It is for one record passed in from a front-end application. This particular stored procedure, (which would probably be better as a UDF, uses the pipe [ | ] character to delimit the characters.

I cannot take full credit, (another guy at a job I used to have started this). What is nice about it is, it can distinguish between text and integers.

IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[ParseDelimitedList]')

AND OBJECTPROPERTY( id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[ParseDelimitedList]

GO

CREATE PROCEDURE ParseDelimitedList

@PipeList varchar(2000),

@ColumnType varchar(7) = 'int'

AS

SET NOCOUNT ON

DECLARE @Delimiter char(1),

@LeftDelimter smallint,

@Item varchar(50),

@List varchar(8000)

CREATE TABLE #List( ItemID varchar(25) NOT NULL)

IF NOT( @PipeList IS NULL OR @PipeList = '0' OR LEN( RTRIM( @PipeList)) < 1)

BEGIN

SELECT @LeftDelimter = 1,

@Delimiter = '|',

@List = @Delimiter + @PipeList + @Delimiter

WHILE CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) > 0

BEGIN

SELECT @Item = SUBSTRING( @List, @LeftDelimter + 1, CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) - ( @LeftDelimter + 1))

IF LTRIM( RTRIM( @Item)) <> ''

BEGIN

INSERT INTO #List( ItemID) VALUES( @Item)

END

SELECT @LeftDelimter = CHARINDEX( @Delimiter, @List, @LeftDelimter + 1)

END

IF @ColumnType = 'int'

BEGIN

SELECT CONVERT( int, ItemID) AS 'ItemID' FROM #List

END

ELSE

BEGIN

SELECT ItemID FROM #List

END

SET NOCOUNT OFF

I wasn't born stupid - I had to study.