need sql query to convert a column of a table into meaningful data

Question

Post reply

need sql query to convert a column of a table into meaningful data

freecoder

SSC Eights!

Points: 951
More actions
December 7, 2011 at 2:27 am

#249222

splitter needed

Viewing 15 posts - 1 through 15 (of 44 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I would use a splitter function like this one:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

You could split on " " and the split again on "=".

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You can certainly do this with TSQL - using the splitter Gianluca mentioned - but it's an expensive long-haul trip:

DECLARE @Message VARCHAR(8000)

SET @Message = 'Type=0 8=111 9=HSBC 35=d 49=DAC 56=ALA 34=70'

SELECT

split.Item,

[Entity code] = LEFT(split.Item,x.pos-1),

en.[Entity name],

Attribute = SUBSTRING(split.Item,x.pos+1,LEN(split.Item)-x.pos)

FROM dbo.Split(@Message,' ') split

CROSS APPLY (SELECT Pos = CHARINDEX('=',split.Item)) x

LEFT JOIN (

SELECT [Entity code] = '9', [Entity name] = 'Bank name' UNION ALL

SELECT '35', 'Type' UNION ALL

SELECT '49', 'Merchant code'

) en ON en.[Entity code] = LEFT(split.Item,x.pos-1)

Results:

[font="Courier New"]

ItemEntity codeEntity nameAttribute

Type=0Type NULL 0

8=111 8 NULL 111

9=HSBC 9 Bank name HSBC

35=d35 Type d

49=DAC49 Merchant code DAC

56=ALA56 NULL ALA

34=7034 NULL 70

[/font]

Note that this doesn't even pivot out the result into your requested format.

Most folks would recommend performing this degree of data massage in the presentation layer rather than the db.

Edit: added results

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

ChrisM@Work (12/7/2011)
Most folks would recommend performing this degree of data massage in the presentation layer rather than the db.

That's good advice.

Another option could be a CLR function, which seems to perform much better on this kind of task.

-- Gianluca Sartori

freecoder SSC Eights! Points: 951 More actions · Answer 4

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 3:15 am

#1417900

ok

freecoder SSC Eights! Points: 951 More actions · Answer 5

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 3:23 am

#1417906

k

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

mario.balatellii (12/7/2011)
sorry mike i may sound stupid but where do i get object dbo.Split. and second question is that declaring @message get only on row but in my table if have like more than five hundred thousand row. Thanks for ur help.

There's no Mike contributing yet Mario 🙂

Have a quick read of the link in my sig (click on the highlighted word this), it will show you how best to ask a question in order to maximise your chances of good solid tested code. The bit that Gianluca and I are most interested in at this point is a sample table containing a few rows of your data. Fitting the above code to a sample table will take moments.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 7

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 3:45 am

#1417921

cool.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Heh Mario, take a look at the second paragraph in the forum etiquette article I pointed out to you! You might then read the third 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 9

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 4:03 am

#1417931

ok

freecoder SSC Eights! Points: 951 More actions · Answer 10

Hope this information is enough for you chris? thnks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

mario.balatellii (12/7/2011)
Hope this information is enough for you chris? thnks

Have you tried running the sample table code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 12

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 4:33 am

#1417948

do appologise chris 🙁

freecoder SSC Eights! Points: 951 More actions · Answer 13

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 4:37 am

#1417950

will send it tomoro

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Many thanks, Mario, that's just about perfect. Here's your table integrated into that code I posted earlier. First thing you will notice is that the data requires partitioning - there's nothing to identify one row of data from another. You have a couple of columns you can use - you decide. Second thing is the string-splitter, dbo.split(). This is the CLR string splitter developed by Paul White for Jeff Moden's string-splitter article. Use the CLR string-splitter for this (if you have CLR permissions etc) because it's faster than the TSQL version. You can find them here[/url], same link that Luca posted.

DROP TABLE #SampleTable

CREATE TABLE #SampleTable(

[No.] [int] NOT NULL,

[MsgType] [varchar](2) NULL,

[MsgTimeStamp] [datetime] NULL,

[Description] [varchar](500) NULL

) ON [PRIMARY]

GO

insert into #SampleTable([No.],MsgType,MsgTimeStamp,[Description])

values(294,8,GETDATE(), '9=99 35=5 49=HSBC 56=M&S 34=293'),

(294,8,GETDATE(), '9=99 35=5 49=BARC 56=M&S 34=293'),

(294,8,GETDATE(), '9=99 35=5 49=HSBC 56=DEB 34=293'),

(294,8,GETDATE(), '9=99 35=5 49=HSBC 56=NEXT 34=293')

SELECT [No.], MsgType, MsgTimeStamp,

split.Item,

[Entity code] = LEFT(split.Item,x.pos-1),

en.[Entity name],

Attribute = SUBSTRING(split.Item,x.pos+1,LEN(split.Item)-x.pos)

FROM #SampleTable s

CROSS APPLY dbo.Split(s.[Description],' ') split

CROSS APPLY (SELECT Pos = CHARINDEX('=',split.Item)) x

LEFT JOIN (

SELECT [Entity code] = '9', [Entity name] = 'Bank name' UNION ALL

SELECT '35', 'Type' UNION ALL

SELECT '49', 'Merchant code'

) en ON en.[Entity code] = LEFT(split.Item,x.pos-1)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden