Search This Blog

Remove duplicate characters from a string


Here is its alternate method with Number table approach



create procedure remove_duplicate_characters(@string varchar(100))




as




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 

GO

EXEC remove_duplicate_characters 'aasssarrrty'







returns







asrty 

Count 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='' 









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=''
select    @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

Result
4093978







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' 




select 




         string_col, 




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




from 



         @t






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



Posted by Madhivanan with 15 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))

as




Select @test_param


Go

EXEC test1 'test'


Go

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