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

Viewing 15 posts - 16 through 30 (of 44 total)

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

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

CREATE FUNCTION Spitter

--===== Define I/O parameters

(@pString VARCHAR(8000), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...

-- enough to cover VARCHAR(8000)

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

SELECT t.N+1

FROM cteTally t

WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)

)

--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))

FROM cteStart s

;

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

It's fine for now Mario. There's a slightly newer, faster version, I'll find a link. Or the 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 3

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 5:05 am

#1417971

cool mate! thank you very much for your help.

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

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 5:18 am

#1417978

Is there a way around it.

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

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 5:26 am

#1417981

!

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

You're welcome.

The function looks up to date. Where are we with this? Did you change the sample data to uniquely identify each row?

^{“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 5:35 am

#1417984

I figured out the delimiter thanks

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

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 5:39 am

#1417989

its a space

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

Could be tab. Try copy-paste into a function:

SELECT ASCII('')

SELECT CHAR(7)

Copy and paste the space between the quotes in this - SELECT ASCII('') and run it in SSMS.

^{“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 10

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 5:51 am

#1417997

will try

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

It's possibly worse than that:

56=DEB 34=293131=0018000 47992N1 297=5

There are clearly defined entity-attribute pairs at the beginning and the end, but what's in between? Even by eye it's not possible to tell. It doesn't mean game over, there are ways around this particularly if you know what entity codes you are looking for. However, I'd recommend you spend some time first analysing your data and confirming these findings, because plan A is likely to be more performant than others.

^{“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 6:11 am

#1418017

ok

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

Ah, an overrun, from the bit which records the position in the substring of '='.

SELECT

[No.],

MsgType,

MsgTimeStamp,

split.Item,

Pos,

en.[Entity code],

en.[Entity name],

Attribute

FROM #SampleTable s

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

CROSS APPLY (

SELECT

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

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

Pos

FROM (SELECT Pos = CHARINDEX('=',split.Item) ) d

WHERE d.pos < DATALENGTH(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] = x.[Entity code]

Edit: changed dbo.StringSplitter to dbo.DelimitedSplit8k

^{“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 14

freecoder

SSC Eights!

Points: 951

December 7, 2011 at 7:03 am

#1418069

cool..try it later.

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

This mean instead of getting 10 rows for 1 description column. I think we can use Max function to do this.