Sunday, December 19, 2010

SQL Server SUBSTRING giving me incorrect results

I get incorrect result when i try to do a substring within a stored proc,but the same thing if i try it in a query window gives me proper results.

declare @FinalCol_List as varchar(100)
Set @FinalCol_List= '11,22,33,bb,'
print @FinalCol_List
IF LEN(@FinalCol_List) > 0
SET @FinalCol_List = SUBSTRING(@FinalCol_List, 1, LEN(@FinalCol_List) - 1)

print @FinalCol_List

Tuesday, February 23, 2010

Using SQL Remove the Last character in a String

There are cases when we may need to remove only the last character after we have done some concatenation.
In SQL Query we can do it using Substring and Left


SUBSTRING (Text,Start_From,Till_lenght)

Remove a comma, at the end of the string

Eg: select substring('1,2,3,4,5,', 1, (len('1,2,3,4,5,') - 1))

LEFT gives and output of the string from starting to end

Eg: SELECT LEFT(String, LEN(String) - 1)

The same can be used to trim data from the Right
Eg: SELECT RIGHT(String, LEN(String) - 1)