Learning through osmosis. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Learning through osmosis.

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-19 : 14:58:03

I have a colleague who, with her eyes closed, and both arms severed, can code circles around anyone else here. I try to learn as much as a can from her, but she's not exactly "availble" to train someone like me. I'm not claiming the code below is some glorious example of her skills, but i would love someone to walk me through what the code below is doing. I think of it as the Rosetta Stone for myself, as understanding something like that which is below would allow me to understand much more. In fact, i'll provide anyone who can provide me a coherent walk through for this code, a batch of my famous Jack Daniels chocolate chip cookies. that's right, cookies made with the best Tennessee sipping whiskey ever.


SELECT [InmateKey],[InputDate],LTRIM(RTRIM(LEFT([Alias],CHARINDEX(',',[Alias])-1))) AS [Alias],DOB,SSN,UNIQUEKEY
  FROM #ALIAS WHERE CHARINDEX(',',[Alias]) <> 0 AND CHARINDEX(' ',LTRIM(RTRIM(LEFT([Alias],CHARINDEX(',',[Alias])-1)))) <> 0 
AND CHARINDEX(' ',LTRIM(RTRIM(SUBSTRING([Alias],CHARINDEX(',',[Alias])+1,300)))) <> 0
 UNION
SELECT [InmateKey],[InputDate],CASE WHEN RIGHT(LTRIM(RTRIM(SUBSTRING([Alias],CHARINDEX(',',[Alias])+1,300))),1) = ',' 
THEN LTRIM(RTRIM(LEFT(LTRIM(RTRIM(SUBSTRING([Alias],CHARINDEX(',',[Alias])+1,300))),LEN(LTRIM(RTRIM(SUBSTRING([Alias],CHARINDEX(',',[Alias])+1,300))))-1)))
 ELSE LTRIM(RTRIM(SUBSTRING([Alias],CHARINDEX(',',[Alias])+1,300))) END AS [Alias],DOB,SSN, 
SCADMIN.dbo.fnsc_GetUniqueID(NEWID()) AS UNIQUEKEY
  FROM #ALIAS WHERE CHARINDEX(',',[Alias]) <> 0 AND CHARINDEX(' ',LTRIM(RTRIM(LEFT([Alias],CHARINDEX(',',[Alias])-1)))) <> 0 
AND CHARINDEX(' ',LTRIM(RTRIM(SUBSTRING([Alias],CHARINDEX(',',[Alias])+1,300)))) <> 0

edit: fixed long lines

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 15:54:08

1. She's using temp tables...in my book usually means that's a bad thing..not that they don't have their place..but I'm guessing (and betting hundreds of dollars), no
2. She's Ass U Ming that everything before a comma is an alias..I don't know if I'd take that bet. This means, that the column is most likely denormalized and hence lacking appropriate constraints..I have to go to a meeting, but I will look more

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-19 : 16:10:15

I can break out one of the expressions and hopefully provide enough for you to extend to the others:

SELECT [InmateKey],[InputDate],
LTRIM(         -- trim spaces from left
RTRIM(         -- trim spaces from right
LEFT(          -- of all the characters to the left
[Alias]        -- of the Alias column
,CHARINDEX(',' -- up to the position of a comma
,[Alias]       -- in the Alias column
)-1)))         -- but go back one position to "trim" the comma itself 
AS [Alias]     -- and call this expression "Alias" (in other words, make "Alias" the alias for the Alias column)

I'd worry less about temp tables than I would about the verbosity of the code, and the fact that she appears to be parsing multiple CSV values from a string (as Brett mentioned about the denormalized column)

There's a technique listed here: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

That parses any number of CSVs into multiple rows. You should also Google "Jeff Moden CSV" and check out his technique as it performs the same function and performs better with larger strings. Either of these should accomplish the same thing without requiring a UNION ALL or even temp tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources