CODEWITHSUNDEEP
sql-servert-sqlsubstring
Cl_3518233
Cl_3518233

Reputation: 25

Remove last character from string column in SQL

I have these special characters " ||~|| " at the end of each value in column X. I need to remove these special characters.

Right now I am using this syntax, but it doesn't seem to accomplish the task for all rows.

set [Customer Num] = substring(ltrim(rtrim([Customer Num])),3,len([Customer Num]))

Upvotes: 1

Views: 16013

Answers (4)

Haider Ali
Haider Ali

Reputation: 13

This works 

update [Table] set [Customer Num] = (substring(ltrim(rtrim([Customer Num])),0,len([Customer Num]) - 3))
where [Customer Num]  like '%only text containing this string%'

Upvotes: 0

collapsar
collapsar

Reputation: 17268

If you know for sure that your values end with the Special String, try

substring ( [Customer Num], 1, length([Customer Num]) - length(' ||~|| ') )

It's better, however, to safeguard against accidental Deletions:

substring ( 
      [Customer Num]
    , 1
    , Case coalesce(substr( [Customer Num], length([Customer Num]) - length(' ||~|| '), '_' )
          When ' ||~|| ' then length([Customer Num]) - length(' ||~|| ') 
          Else                length([Customer Num])
      End
)

If your rdbms Supports regular expressions, this simplifies to (using Oracle Syntax)

Regexp_replace ( [Customer Num], ' \|\|~\|\| $', '')

Upvotes: 1

AK47
AK47

Reputation: 3807

Try this options,

Declare @myStr varchar(50) = 'amol~'

--If want to remove char ~ of any position

Select REPLACE(@myStr,'~','')

Set @myStr = '~amol~'
Select REPLACE(@myStr,'~','')

Set @myStr = '~am~ol~'
Select REPLACE(@myStr,'~','')

--If want to remove character ~ at Last position & existance of char ~ is inconsistent Set @myStr ='amol~'

Select Case When RIGHT(@myStr,1) = '~'
            Then LEFT(@myStr,len(@myStr) - 1)
            Else @myStr
        End

If you are looking to replace ||~|| Then try this,

Declare @myStr varchar(50) = 'amol ||~|| '

--If want to remove string ||~| of any position
Select REPLACE(@myStr,'||~||','')
Set @myStr = '||~||amol||~||'
Select REPLACE(@myStr,'||~||','')
Set @myStr = '||~||am||~||ol||~||'
Select REPLACE(@myStr,'||~||','')

--If want to remove string ||~| at Last position & existance of char ||~| is inconsistent
Set @myStr ='amol||~||'

Select Case When RIGHT(@myStr,5) = '||~||'
            Then LEFT(@myStr,len(@myStr) - 5)
            Else @myStr
        End

Upvotes: 5

Kaleel
Kaleel

Reputation: 321

Assuming you have to remove last 3 characters of ColumnX

set [ColumnX] = substring(ltrim(rtrim([ColumnX])),0,len([ColumnX]) - 3)

Upvotes: 0

Related Questions