Trim Left Characters in SQL Server

trim left characters in sql server?

select substring( field, 1, 5 ) from sometable

How do I remove the first characters of a specific column in a table?

SELECT RIGHT(MyColumn, LEN(MyColumn) - 4) AS MyTrimmedColumn

Edit:
To explain, RIGHT takes 2 arguments - the string (or column) to operate on, and the number of characters to return (starting at the "right" side of the string). LEN returns the length of the column data, and we subtract four so that our RIGHT function leaves the leftmost 4 characters "behind".

Hope this makes sense.

Edit again - I just read Andrew's response, and he may very well have interperpereted correctly, and I might be mistaken. If this is the case (and you want to UPDATE the table rather than just return doctored results), you can do this:

UPDATE MyTable
SET MyColumn = RIGHT(MyColumn, LEN(MyColumn) - 4)

He's on the right track, but his solution will keep the 4 characters at the start of the string, rather than discarding said 4 characters.

T-SQL - How to remove text to the left of a specific character

This was the answer that worked for me. Script.Topic is the column name:

SELECT RIGHT('{Script.Topic}', len('{Script.Topic}') - charindex(':', '{Script.Topic}'))

trim values from left using SQL

SELECT SUBSTRING('ABC0005953', 4, LEN('ABC0005953'))

Start at the fourth character and keep going.

(Just posting as an alternative to the RIGHT(...) solution.)

In response to your update, I assume you mean you want to apply the above to your table:

SELECT SUBSTRING(TABLE.VALUE, 4, LEN(TABLE.VALUE))
FROM TABLE

From your other question:

I have the following:

SELECT DISTINCT

Left(GIFTHEADER.pID + GIFTHEADER.PID + '-' + Cast(PAYMENTDETAIL.PLINENO as Varchar),18)

AS TRANSACTIONREF...

Currently my value looks like this:

ABC0005953ABC0005953

I want to simply strip off the first 4 characters from GIFTHEADER.pID

If you want to remove the first four characters from GIFTHEADER.pID, I would recommend removing them before putting the value into your combined string:

SELECT DISTINCT
    LEFT(SUBSTRING(GIFTHEADER.pID, 5, LEN(GIFTHEADER.pID) +
        GIFTHEADER.PID +
        '-' +
        Cast(PAYMENTDETAIL.PLINENO as Varchar),18)
    AS TRANSACTIONREF

need to trim first 11 characters off a string in SQL

SELECT STUFF(SomeString,1,11,'')

(obligatory link)

Trim a character on SQL

Your try was very close.
All you needed to add was -1.

As you can see the explanation for the left() function:

The LEFT() function extracts a number of characters from a string
(starting from left): LEFT(string, number_of_chars)

With charindex() function you have told the left() function how many characters to take. By adding -1 to that you have removed the '/' sign because you have told the LEFT() function to take 10 characters and not 11(for example) because 11th character is '/'.

select left('510325205/000/01',charindex('/','510325205/000/01')-1)

or because you have column named code

select left(code,charindex('/',code)-1)

If you have values without / you can use this:

select case when charindex('/',code_c)-1  = -1
            then 
            code_c
            else
            left(code_c,charindex('/',code_c)-1) 
            end RESULT
from test

OR

select left(code_c,iif(charindex('/',code_c)-1 = -1, len(code_c), charindex('/',code_c)-1)) 
from test

Here is DEMO

How to remove everything before a certain character in SQL Server?

Try this:

right(MyColumn, len(MyColumn) - charindex('-', MyColumn))

Charindex finds the location of the hyphen, len finds the length of the whole string, and right returns the specified number of characters from the right of the string.

SQL Server replace, remove all after certain character

Use LEFT combined with CHARINDEX:

UPDATE MyTable
SET MyText = LEFT(MyText, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

Note that the WHERE clause skips updating rows in which there is no semicolon.

Here is some code to verify the SQL above works:

declare @MyTable table ([id] int primary key clustered, MyText varchar(100))
insert into @MyTable ([id], MyText)
select 1, 'some text; some more text'
union all select 2, 'text again; even more text'
union all select 3, 'text without a semicolon'
union all select 4, null -- test NULLs
union all select 5, '' -- test empty string
union all select 6, 'test 3 semicolons; second part; third part;'
union all select 7, ';' -- test semicolon by itself    

UPDATE @MyTable
SET MyText = LEFT(MyText, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

select * from @MyTable

I get the following results:

id MyText
-- -------------------------
1  some text
2  text again
3  text without a semicolon
4  NULL
5        (empty string)
6  test 3 semicolons
7        (empty string)

Remove specific character from string (SQL Server 2012)

You can use replace() to do this. In this case it will search for 'B' and replace it with an empty string -> ''. Please note that this function will replace all 'B' from this column.

 SELECT 
       REPLACE([Port_Ticker], 'B', '') as "Fund"
      ,[BENCH_Ticker] as "Index ID"
      ,format(cast([VALUE_DATE] as Date),'dd/MM/yyyy') as "Updat"
      ,([Port_Risk_Contrib] / 10000000) as "StDev Fund"
      ,([Active_risk_contrib] / 10000000) as "TE"
      ,([BENCH_RISK_CONTRIB] / 100) as "StDev BM"
  FROM [DM_PORTFOLIO_ANALYSIS].[basedata].[PortfolioRiskFigures]
  where [BLOCK_FACTOR] = 'Total'
  and [Port_ticker] = 'B1023'
  order by [VALUE_DATE] asc

SQL Server TRIM character

LEFT('BOB*', LEN('BOB*')-1)

should do it.

---

Related Topics