CODEWITHSUNDEEP
sqlsubstringreversedelimitercharindex
JK14
JK14

Reputation: 3

To return text value between the first and second semicolons counting from the right of a string

I have a field in a table which consist of a string of values separated by semi-colons, e.g. apple; banana; orange; pear

I have been trying to build a SELECT statement to return the second group in between first and second semi-colons counting from the right (and if there is only 1 semi-colon will return null for now).

For example, orange.

I have been testing and trying ChatGPT but the closest I have gotten is of below, which returns everything right of the second semi-colon counting from the right.

As example: orange; pear

I just cannot figure out what I should do to not show anything value right of the first semi-colon on the right. Or could it be my dataset issue, as I do see sometimes commas were used instead of semi-colons but that could be another story too.

LTRIM(RTRIM(
           CASE
               -- Ensure there are more than two semicolons
               WHEN LEN(@string) - LEN(REPLACE(@string, ';', '')) >= 2
               THEN SUBSTRING(
                   @string,
                   LEN(@string) - CHARINDEX(';', REVERSE(@string), CHARINDEX(';', REVERSE(@string)) + 1) + 2,
                   CHARINDEX(';', REVERSE(@string), CHARINDEX(';', REVERSE(@string)) + 1) - 1
               )
               ELSE ''
           END
       )) AS SecondGroupFromRight

Would really appreciate some help. Many thanks in advance

Upvotes: 0

Views: 86

Answers (4)

GuidoG
GuidoG

Reputation: 12059

This answer is for sql server
Another way is to split them into seperate rows, and then retrieve the 2nd last row
We can use the row_number() to give each seperate row a sequential number

declare @split nchar(1) = ';'
declare @val nvarchar(2000) = 'apple; banana; orange; pear'

select t.value
from   ( select trim(value) as value,
                ROW_NUMBER() over(order by (select 1)) as rn,
                (select count(value) from string_split(@val, @split)) as total
                from   string_split(@val, @split)
       ) t
where t.total > 2
and   t.rn = t.total - 1

See this dbFiddle

  • trim(value) to get rid of the space in front of the values
  • order by (select 1) in the row_number to avoid sorting in row_number()
  • select count(value)... to retrieve the total numbers of values
  • now you can simply say t.rn = t.total - 1 to retrieve the 2nd last row
  • where t.total > 2 so you have no result when less then 3 values
values result
declare @val nvarchar(2000) = 'apple; banana; orange; pear' orange
declare @val nvarchar(2000) = 'apple; banana; orange' banana
declare @val nvarchar(2000) = 'apple; banana'
declare @val nvarchar(2000) = 'apple'

And while this is all nice, but how to use this when @val would be a column in a table ?

well, like this

declare @split nchar(1) = ';'
  
select test.id,
       ( select t.value
         from   ( select trim(value) as value,
                         ROW_NUMBER() over(order by (select 1)) as rn,
                         (select count(value) from string_split(test.fruit, @split)) as total
                         from   string_split(test.fruit, @split)
                ) t
          where t.total > 2
          and   t.rn = t.total - 1
       ) value,
       test.othercolumn
from   test

See this dbFiddle for a working example

result could be

id value othercolumn
1 orange hello world
2 grapes how are you

Upvotes: 0

ValNik
ValNik

Reputation: 5916

If OP use charindex function, perhaps this is SQL Server.
It is unknown is the enable_ordinal option is available.
So, with charindex and patindex, expression is

  case when patindex('%;%;%', reverse(string))>0 then
      trim(reverse(substring(reverse(string),patindex('%;%;%', reverse(string))+1
              ,charindex(';', reverse(string),patindex('%;%;%', reverse(string))+1)
                -patindex('%;%;%', reverse(string))-1
             )))
  else ''
  end s2r
  1. Check is consist second right (reverse) part.
patindex('%;%;%', reverse(string))>0
  1. Extract part from patindex('%;%;%' ...) to next charindex(';'...)

See example

id string
1 apple; banana; orange; pear
2 apple; banana
3 apple 
select *
  ,case when patindex('%;%;%', reverse(string))>0 then
      trim(reverse(substring(reverse(string),patindex('%;%;%', reverse(string))+1
              ,charindex(';', reverse(string),patindex('%;%;%', reverse(string))+1)
                -patindex('%;%;%', reverse(string))-1
             )))
   else ''
   end s2r
from test
id string s2r
1 apple; banana; orange; pear orange
2 apple; banana
3 apple

fiddle

Upvotes: 0

Idoneus
Idoneus

Reputation: 86

try this:

--declare @val nvarchar(2000) = 'apple; banana; orange; pear'
declare @val nvarchar(2000) = ';apple'
drop table if exists #x

select IDENTITY(INT,1,1) AS ID, * 

into #x
from string_split(reverse(@val), ';')


select nullif(reverse(value), '') as [2ndVal] from #x where id = 2

Upvotes: 0

Pandav Kishan
Pandav Kishan

Reputation: 1

SELECT 
    CASE 
        -- Ensure there are at least two semicolons
        WHEN LEN(@string) - LEN(REPLACE(@string, ';', '')) >= 2 
        THEN 
            -- Extract the part between the second-last and last semicolon
            SUBSTRING(
                @string,
                LEN(@string) - CHARINDEX(';', REVERSE(@string), CHARINDEX(';', REVERSE(@string)) + 1) + 2,
                CHARINDEX(';', REVERSE(@string), CHARINDEX(';', REVERSE(@string)) + 1) 
                - CHARINDEX(';', REVERSE(@string)) - 1
            )
        ELSE NULL 
    END AS SecondGroupFromRight

Upvotes: -1

Related Questions