SUBSTRING with CHARINDEX

[rose4567]

I am trying to parse a string. I am quering a field called ACCOUNT_CODE from a table called PO_ACCOUNT.

Each account code has 7 segment names and each segment is divided by the tilda symbol:
INDEX~OBJ~USR~GRANTCD~GRANTDTL~PROJCD~PROJDTL

Not all segments are required so the data in the field could be completely populated like:
DV709790~0654~956~100~225863~899045~0097611
Or only partially populated like:
DV709790~0654~956~100~ ~ ~

I need to query this data and so that each segment is it's own column name. Each segment varies in length (from 3 to 6 in length) making it impossible to use standard LEFT or RIGHT functions.

My SQL is not good enough yet to understand how to grab all seven segments and query from the table. Any input is greatly appreciated!

 

[sqlfable]

You can use recursive sql and a bit of FOR XML fun. I owe this to a member here, gmmastros I think. I found it in these forums months ago and it's come in handy replacing slow Split() functions.

My attempt at an example

;with results as (
Select
cast('<stringVal>' + replace('INDEX~OBJ~USR~GRANTCD~GRANTDTL~PROJCD~PROJDTL', '~', '</stringVal><stringVal>') + '</stringVal>' as xml) as xml_string
)
SELECT
x.i.value('.', 'varchar(30)') as string_result
FROM results
CROSS APPLY xml_string.nodes('//stringVal') x(i)

 

[hwkranger]

sqlfable -- I liked your neat little trick. I might use it in the future.

rose -- an alternative is:

			DECLARE @string nvarchar(100)
			SET @string = 'INDEX~OBJ~USR~GRANTCD~GRANTDTL~PROJCD~PROJDTL
'
			DECLARE @result table (string nvarchar(100))
			DECLARE @Num		int
			DECLARE @Pos		int
			DECLARE @NextPos	int
			--DECLARE @UserID		int

			SET @Num = 0
			SET @Pos = 1
			WHILE(@Pos <= LEN(@string))
			BEGIN
				SELECT @NextPos = CHARINDEX(N'~', @string,  @Pos)
				IF (@NextPos = 0 OR @NextPos IS NULL)
					SELECT @NextPos = LEN(@string) + 1
				INSERT INTO @Result VALUES( RTRIM(LTRIM(SUBSTRING(@string, @Pos, @NextPos - @Pos))))


				SELECT @Pos = @NextPos+1
				SET @Num = @Num + 1
			END

			SELECT * FROM @result

Not nearly as nice, not nearly as pretty, but it does avoid XML. LOL.

 

[rose4567]

Thanks sqlfable, but the result set returns a list. I need everything on a single row.

string_result
INDEX
OBJ
USR
GRANTCD
GRANTDTL
PROJCD
PROJDTL

My apologies if I was not clear. I am new to this.

Thank you so much to both of you for the suggestions, but I am stumped on how to apply either of these code samples so they call to a specific table and field.

Here is a sample of how data in PO_ACCOUNT.ACCOUNT_CODE is stored in the field:
DV709790~0654~956~100~225863~899045~0097611

This is the result I am hoping for spread across separate fields in a single row:
INDEX OBJ USR GRANT GRANTDTL PROJ PROJDTL
DV709790 0654 956 100 225863 889045 0097611

 

[gmmastros]

Take a look at this thread:

http://tek-tips.com/viewthread.cfm?qid=1451986

In it, I show a 'ParsePart' function that you can use to write this query.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom