get last _ position values in sql server

Question

Hi I have one doubt in sql server .

how to get first position to right side specific character position.

table : empfiles

filename: 
ab_re_uk_u_20101001
ax_by_us_19991001
abc_20181002

I want output like below:

filename 
ab_re_uk_u
ax_by_us
abc

I tried like below :

select SUBSTRING(filename,1,CHARINDEX('2',filename) - 1)  as filename  from empfiles

above query is not given expected result please tell me how to write query to achive this task in sql server .

Take a look at [this answer](https://stackoverflow.com/a/39002164/5443550), which explains how to get everything to the left of the last `_` character — Diado, Jun 05 '18 at 10:34

Yogesh Sharma · Answer 1 · 2018-06-05T10:41:51.337

1

If last position has always numeric values then you can use patindex():

select *, substring(filename, 1, patindex('%[0-9]%', filename)-2) as NewFile
from empfiles e;

If you want to get characters after than _ to right sight of string then you can use combo to reverse() and substring()

select *,
      reverse(substring(reverse(filename),charindex('_', reverse(filename))+1, len(filename)))
from empfiles e;

edited Jun 05 '18 at 10:41

answered Jun 05 '18 at 10:34

Yogesh Sharma

47,987
5
21
48

score 0 · Answer 2 · answered Jun 05 '18 at 10:46

Another way is to use reverse in combination with STUFF.

create table f(filename nvarchar(100));
insert into f values
('ab_re_uk_u_20101001')
,('ax_by_us_19991001')
,('abc_20181002');

select 
    filename=reverse(stuff(reverse(filename),1,charindex('_',reverse(filename)),''))
from f

score 0 · Answer 3 · answered Jun 05 '18 at 11:16

Try This

CREATE TABLE #DATA([FILENAME] NVARCHAR(100));
INSERT INTO #DATA VALUES
('ab_re_uk_u_20101001')
,('ax_by_us_19991001')
,('abc_20181002');

SELECT [filename],
       SUBSTRING([filename],0,PATINDEX('%[0-9]%',[filename])-1) AS ExpectedResult 
FROM #Data

Result

filename                ExpectedResult
--------------------------------------
ab_re_uk_u_20101001     ab_re_uk_u
ax_by_us_19991001       ax_by_us
abc_20181002            abc

score 0 · Answer 4 · answered Jun 05 '18 at 11:56

Well, obviously the last position value is a date, and the format is YYYYMMDD so its 8 characters, plus, added by underscore character, so that makes its 9 character.

Assumed by the above statement applied, the following logic of the query should work

SELECT SUBSTRING(ColumnText, 1, LEN(ColumnText) - 9)

Which means, only display characters from character position 1, to character position LEN - 9, which LEN is the length of characters, and 9 is the last 9 digit of number to be removed

score 0 · Answer 5 · answered Jun 05 '18 at 12:01

Try with this ..

select [filename],SUBSTRING([filename],1,PATINDEX('%_[0-9]%',[filename])-1) from empfiles

Individual Select records

SELECT SUBSTRING('ab_re_uk_u_20101001',1,PATINDEX('%_[0-9]%','ab_re_uk_u_20101001')-1)

SELECT SUBSTRING('ax_by_us_19991001',1,PATINDEX('%_[0-9]%','ax_by_us_19991001')-1)

SELECT SUBSTRING('abc_20181002',1,PATINDEX('%_[0-9]%','abc_20181002')-1)

get last _ position values in sql server

5 Answers5