Miscellaneous Ramblings on Hacking

July 15, 2009

Using XML datatype to parse comma-delimited columns

Filed under: Uncategorized — Zack Bethem @ 5:20 am

This was the coolest trick I’ve seen in a while.
Problem: need to parse a comma delimited file

select email
, Split.a.value('.','Varchar(50)') as kid_age
, row_number() over (partition by email order by email) as row_cnt
from (
select email
, cast('' + REPLACE(kid_age,',','') + '' as xml) as kid_age
from stg_profile_data ) x
cross apply kid_age.nodes('/M') split(a)

Mucho thanks to where Google found it


July 3, 2009

I Heart Unix

Filed under: Uncategorized — Zack Bethem @ 7:35 pm

Use SED to get rid of those nasty empty lines in a text file:

sed '/^$/ d' someFile > someNewFile
mv someNewFile someFile

Create a free website or blog at WordPress.com.