remove last n characters from a varchar in SQL-CodePudding

I am trying to remove the last n characters from a string. I tried this:

replace( str, right(str, 3), '' )

But it fails on str where the pattern repeats more than once. 888106106. In this case I get 888, instead of 888106

Now I am using

left (str, length(str)-3)

Is there a more efficient away of achieving this?

CodePudding user response：

In SQL Server, that's the most effective way.

Note that you should do one of:

assert that all input strings will be length>2 [a bit lazy]
handle the error where 1 of the rows has a length<3 and the query terminates early [a bit shoddy]
use a case statement to handle the case where length < 3 [the preferred approach]

CASE WHEN LENGTH(str) > 2 THEN LEFT(str, LENGTH(str) - 3) ELSE str END

Other flavours of SQL you may have to work without the case statement.

CodePudding user response：

If you fancy a regex based solution:

regexp_replace(str,'...$','')

It will leave strings with < 3 characters unchanged

CodePudding user response：

So checking that LEFT and/or SUBSTR work equally (I assume LEFT is faster):

select  
    column1 
    ,left(column1, length(column1) -3) as r1
    ,substr(column1, 0, length(column1) -3) as r2
from values
    ('abc123')
    ,('ab123')
    ,('a123')
    ,('123')
    ,('12')
    ,('1')
    ,('')
    ,(null);

gives:

COLUMN1	R1	R2
abc123	abc	abc
ab123	ab	ab
a123	a	a
123	null	null
12	null	null
1	null	null
''	null	null
null	null	null

so no checks are needed, nice to know.

if you do some perf testing:

create database test;
create schema test.test;

create or replace table test.test.many_string as
select seq8()::text as a
from table(generator(ROWCOUNT => 10000000));

ALTER SESSION SET USE_CACHED_RESULT = false;

select sum(length(left(a, length(a) -3))) from test.test.many_string;
select sum(length(substr(a, 0, length(a) -3))) from test.test.many_string;

after running them both a couple of times on my x-small, I get results in the order of 300ms, so these are equal.

So it seems you have a fast solution, and easy to read.