trim leading character from column

,

this procedure takes a character, tablename and columnname as input-parameter; on execution it removes the given leading character from the table-column - as well as leading blanks. small and easy but very helpful.

create procedure

p_trim_first_character_column
   (
      @character char(1),
      @table     nvarchar(255),
      @column    nvarchar(50)
   )
as
-----------------------------------------------------------------------------------------
-- created by Kerstin Grobst
-- procedure deletes any leading character from a table column and leading blanks
-- in-parameter: @character = character to delete (e.g. '*')
--               @table     = target table
--               @column    = target column
-- execution example: exec dbo.p_trim_first_character_column '*','table', 'textcolumn'
-----------------------------------------------------------------------------------------


declare @sqlstring nvarchar(2000)

begin

-- build select statement and execute it
set @sqlstring = 
' 
update 
   '+ @table + '
set 
   '+ @column +' = ltrim
                  (
                    case
                       when left('+ @column+ ',1) like '''+ @character + ''' then STUFF ( '+ @column +' , 1 , 1 , '''')
                    else
                       '+ @column + '
                    end
                   )
'

execute sp_executesql @sqlstring

end

Rate

Share

Share

Rate