Converting a List to table

This function accepts a String which holds list of id's and returns a table and then you can use this table to join and filter main table data. You can pass the list with your choice of separator.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


Create function [dbo].[ListToTable]
( @list varchar(4000), @separator varchar(10) )

/* this function receives a @separator delimited list and outputs a table with
each item in the list as a row in a table so the following can be used
SELECT * from dbo.Customers
inner join listToTable( '123,456,789', ',' )
on CustomerID = listValue
*/

returns @listTable table( listValue Int null )
as
begin

if @list is null
insert into @listTable values( null )

-- @list is the list we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @list_value varchar(1000) -- this holds each list value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each list value
set @list = @list + @separator

-- Loop through the string searching for separator characters
while patindex('%' + @separator + '%' , @list) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @list)
select @list_value = left(@list, @separator_position - 1)

insert into @listTable values( @list_value )

-- This replaces what we just processed with and empty string
select @list = stuff(@list, 1, @separator_position, '')
end

return

end

Finding file extension in SQL

Here is the SQL code to find the extension of a file:

declare @appurl varchar(50)
set @appurl= reverse('philip.singh.bedi.doc')

select reverse(LEFT(@appurl, CHARINDEX('.', @appurl) - 1)) ext,
reverse(RIGHT(@appurl, LEN(@appurl) - CHARINDEX('.', @appurl))) fname

Visit to Portishead

I went to Portishead, a small town near Bristol to see Jatin and work on one web service, Jatin is a terrific host and amazing friend, He has superb flat, and it was fun staying with him and working together like old times at Tag. I met with Ian Bale, he is a nice chap and talented professional, I think will learn a lot by working with him.
I stayed in Portishead for 4 days, Jatin has nice set up and working environment, his iMAC inspired me to have one and now I have ordered one and can't wait to try this one, I expect it to be with me in 1-2 weeks.

Lets see how it goes, I need to learn more about MAC, and this has latest powerful operating system - Snow.

Really looking forward to working on this.

:)