Need help to query a non-normalized database

Question

Post reply

Need help to query a non-normalized database

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

CREATE FUNCTION dbo.DelimitedSplit8K

-- 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

;

go

SELECT

lw.name_code, lw.nc_seq, lw.name_type, lw.age, lw.race, lw.sex,

split.ItemNumber,

Item = CAST(REPLACE(QUOTENAME(split.Item,'"'),'"','') AS integer)

FROM [FAYOSSIRMS].[rms].[dbo].[lwnames] as lw

CROSS APPLY dbo.DelimitedSplit8K(lw.vic_crime,',') split

where name_code like 'VI%'

WHERE goes after CROSS APPLY line.

Jared
CE - Microsoft

ckelly 43038 SSC Enthusiast Points: 187 More actions · Answer 2

Thanks, you two!

I flipped the CROSS APPLY line and the WHERE line.

I'm so very close to having my final solution. Yea!! 😀

Now, I get an error message

"Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value '2 1' to data type int."

Here's the vic_crime value that generated the error message:

'1,2 1,2'

A row was created for the value ONE.

Execution stopped when this vic_crime field was encountered--error gen'd.

And a row was not created for the value TWO.

I would think that 3 rows would have been created

row1: ItemNumber=1 Item=1

row2: ItemNumber=2 Item='2 1'

row3: ItemNumber=3 Item=2

Also, I have a question about how the splitter should work.

When the last character in vic_crime is a "comma", zero is assigned to the Item field. And the splitter doesn't eliminate "duplicates".

If the value of vic_crime='1, 1,' then

the result from the final select contains 3 rows:

row1: ItemNumber=1 Item=1

row2: ItemNumber=2 Item=1

row3: ItemNumber=3 Item=0

If the value of vic_crime='1, 1' then

the result from the final select contains 2 rows:

row1: ItemNumber=1 Item=1

row2: ItemNumber=2 Item=1

Any ideas as to how to handle this? In my perfect world, I need to create only ONE row in both scenarios.

row1: ItemNumber=1 Item=1

But if I can stop the creation of rows where Item=0 (last-char is comma), that'll be good enough.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

{EDIT} Sorry... I didn't scroll down to see my own post. I already responded to Pieter's ...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)