Archive for the ‘SQL’ Category

SQL makes decision for you – strange

June 11, 2010 Leave a comment

I ran into a strange MSSQL behaviour.

I wanted to do some calculation and output it in my application like this:

Declare @aFlaot float

set @aFlaot = 3*1/2

select @aFlaot aFlaot

From above I was expecting a float value as I was storing it in a float type variable but it was returning 1 ( as an integer)

And when I tried following I got back right value with correct decimals

Ex 1)

set @aFlaot = 3*1.0/2

select @aFlaot aFlaot

Returned 1.5 (as expected)

Ex 2)

declare @aVal float
set @aVal = 1

set @aFlaot = 3*@aVal/2

select @aFlaot aFlaot

Returned 1.5 (as expected)

What I understand is SQL decides on the basis of values you are using in your calculation not the container type you are using.

Categories: MSSQL, SQL

Converting a List to table

October 13, 2009 Leave a comment

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.


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 )

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

— 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, ”)



Categories: MSSQL, SQL

Finding file extension in SQL

October 13, 2009 Leave a comment

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

Categories: MSSQL, SQL

CF 8 – New Identity COL feature

March 10, 2009 Leave a comment

I like the new feature of getting Identity column value from cfquery in CF*, saved me couple of line of code to get the value from SCOPE_IDENTITY() (MS SQL system variable), you just got to put result name in cfquery tag and then you can get it as resultname.IdentityCol, there is one restriction, you should have just one insert statement in that cfquery, when I had one local query lookup which was saving its result in local @VAR then this IdentityCol thing didn’t work.

let me know if you are not clear about what I am saying, LOL

Categories: ColdFusion, SQL