December 2007 - Posts

Remove duplicate characters from a string

Pinal Dave in his weblog posted about Remove Duplicate Chars From String

Here is its alternate method with Number table approach

create procedure remove_duplicate_characters(@string varchar(100))


Declare @result varchar(100)

set @result=''

select @result=@result+min(substring(@string ,number,1)) from


select number from master..spt_values where type='p' and number between 1 and len(@string )

) as t

group by substring(@string,number,1)

order by min(number)

select @result


EXEC remove_duplicate_characters 'aasssarrrty'





Posted by Madhivanan with no comments

Count number of words in a string

Tony Rogerson in his weblog posted about Counting the number of words in a string

Here are two more methods

1 Using the method I posted already about Squeeze Function to remove multiple spaces

Declare @s varchar(100)

set @s=' See how many words this has '

Select len(string)-len(replace(string,' ',''))+1 as no_of_words from


Select replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','') as string

) as t

2 Using Number table approach used to Extract only numbers from a String

Declare @s varchar(100),@result varchar(100)

set @s=' See how many words this has ' set @result=''

Select @s=replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','')

select count(*)+1 as no_of_words from


select substring(@s,number,1) as number from


select number from master..spt_values where type='p' and number between 1 and len(@s)

) as t

) as t

where number='' 


Posted by Madhivanan with 1 comment(s)

Extract only numbers from a String

Sometimes we may need to extract only numbers from a string. Usually I see people using while loop to check each and every character to see if it is a number and extract it

Here is a different approach

Declare @s varchar(100),@result varchar(100)set @s='as4khd0939sdf78'

set @result=''

    @result=@result+case when number like '[0-9]' then number else '' end from 
         select substring(@s,number,1) as number from 
            select number from master..spt_values where type='p' and number between 1 and len(@s)
        ) as t
    ) as t
select @result as only_numbers


Posted by Madhivanan with 5 comment(s)

Script out Procedures and Functions - Part 2


Part 1 uses information_Schema.routines view and this is based on sp_helptext


declare @sps table(texts varchar(8000))

insert into @sps

select 'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')

order by xtype,name


create table scripts(sps varchar(8000))


declare @texts varchar(1000)

select @texts=min(texts)from @sps

while @texts>''


EXEC('insert into scripts(sps) EXEC '+ @texts)

insert into scripts(sps) select 'GO'

select @texts=min(texts)from @sps where texts>@texts


EXEC master..xp_cmdshell 'bcp "select * from dbname..scripts" queryout "c:\scripts.txt" -c' 

drop table scripts

Posted by Madhivanan with 6 comment(s)
Filed under: ,

Squeeze Function

The Squeeze function is used to remove the multiple occurences of spaces into one occurence. In SQL Server there is no function to do the same. I needed to write this in my application to remove unwanted spaces in the string.

Run the following and see the result

declare @t table(string_col varchar(100))

insert into @t

select 'a b c' union all

select 'a b c' union all

select 'a b c' union all

select 'a b c' union all

select 'a b c'



replace(replace(replace(string_col,' ',' ~!#$^'),'~!#$^ ',''),'~!#$^','') as new_string_col



If you use front end application (VB,.NET,jave,etc), you can also simulate the same approach there

Posted by Madhivanan with 16 comment(s)
Filed under: ,

Column length and Data length

When you use character datatypes(char,varchar,nchar,etc),

always make sure that column has enough length to have data.

Otherwise sometimes you would get errors and sometimes you wont. 


Consider the following example

Declare @t table(i int, test_col varchar(2))

Insert into @t select 1,'test'

Select i,test_col from @t


Well. You get the error

Msg 8152, Level 16, State 14, Line 4

String or binary data would be truncated.

The statement has been terminated.

Because the column length of test_col is not enough to have the value 'test'

But only when you insert data to a table, you get this error.

In other cases you dont

Declare @v varchar(2)

set @v='test'

select @v

Now there is no error but the value is truncated and

only first two characters are assigned to the variable.


The same case is applied when you use a stored procedure with input parameter create procedure test(@test_param varchar(2))


Select @test_param


EXEC test1 'test'


drop procedure test

Now see what happens when you dont specify the length

Declare @v varcharset @v='test'

select @v

By default the length is 1

Consider another example

Select cast('This has more than thirty characters' as varchar)

The result is This has more than thirty char


When you cast a literal to character type without specifying the length,

by default it is 30.


So you need to specify the enough column length when you use character datatypes