trim values from left using SQL

Question

I want to trim the characters from the left in my SQL value:

I have the following value:

ABC0005953

How do i trim the value 3 characters from the left? I would like to see:

Edit my value is:

SELECT LEN(TABLE.VALUE)-3)

possible duplicate of [Fastest way to remove non-numeric characters from a VARCHAR in SQL Server](http://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server) — Meff, May 29 '13 at 13:58

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

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

edited Jun 20 '20 at 09:12

Community

1
1

answered May 29 '13 at 13:57

zimdanen

5,508
7
44
89

@PriceCheaperton: Modified answer based on your `TABLE.VALUE`. – zimdanen May 29 '13 at 14:02
Left ((LEN(COLUMN.ID)-3) + COLUMNHEADER.ID + '-' + Cast(STUFFDETAIL.COLUMNLINENO as Varchar),18) – PriceCheaperton May 29 '13 at 14:05
i NEED TO apply it to that? Sorry once again! – PriceCheaperton May 29 '13 at 14:05
@PriceCheaperton: I'm not sure I understand. You're building a text value and you want to remove.. what from it? – zimdanen May 29 '13 at 14:08
I want to strip away the ABC0... Ie remove the 4 characters from the right. – PriceCheaperton May 29 '13 at 14:09
@PriceCheaperton: Your question says three characters. Just change the `4` to a `5`, and replace `TABLE.VALUE` with your expression. You may want to use a subquery to prevent creating the column more than once. – zimdanen May 29 '13 at 14:18
@PriceCheaperton: Edited based on your other question. – zimdanen May 29 '13 at 14:22
This seems to be truncating my giftheader.pID value... :( – PriceCheaperton May 30 '13 at 09:56

score 2 · Answer 2 · answered May 29 '13 at 13:58

2

You can use STUFF function to replace chars 1 thru 3 with an empty string

SELECT STUFF('ABC0005953',1,3,'')

I believe it's a better and more universal approach than cutting string.

answered May 29 '13 at 13:58

Yuriy Galanter

38,833
15
69
136

score 1 · Answer 3 · answered May 29 '13 at 13:55

1

SELECT RIGHT('ABC0005953', LEN('ABC0005953') - 3)

answered May 29 '13 at 13:55

Meff

5,889
27
36

score 0 · Answer 4 · answered May 29 '13 at 13:57

0

Perhaps you could could just use right(x, len(x)-3)

answered May 29 '13 at 13:57

Sultan

53
1
9

score 0 · Answer 5 · answered May 29 '13 at 13:58

0

Two options for you:

SELECT SUBSTRING('ABC0005953', 5,7)

SELECT RIGHT('ABC0005953', (LEN('ABC0005953') - 4))

answered May 29 '13 at 13:58

CatchingMonkey

1,391
2
14
36

trim values from left using SQL

5 Answers5

Linked

Related