Find Substring in SQL

Question

I have to find substring as follows.

Data as below

aaaa.bbb.ccc.dddd.eee.fff.ggg 

qq.eeddde.rrr.t.hh.jj.jj.hh.hh 

ee.r.t.y.u.i.ii.

I want output as-

bbb

eeeddde

r

challenge I am facing is all have (.) as separator so sub-string is tough to work.

SELECT SUBSTRING(string,CHARINDEX('.',string)+1,
       (((LEN(string))-CHARINDEX('.', REVERSE(string)))-CHARINDEX('.',string))) AS Result 
  FROM [table]

bbb

eeeddde

r

looking substring between first and secound (.)

then it might be between second and third (.)

How do you know if it is between the 1st and 2nd dot or between the 3rd and 4th? — Joakim Danielson, Jul 02 '19 at 13:15

score 2 · Answer 1 · answered Jul 02 '19 at 13:16

2

Here is one method:

select left(v.str1, charindex('.', v.str1 + '.') - 1)
from t cross apply
     (values (stuff(t.string, 1, charindex('.', t.string + '.'), '')
     ) v(str1)

answered Jul 02 '19 at 13:16

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · answered Jul 02 '19 at 13:16

I assume (CHARINDEX) this is ms sql server. CROSS APPLY is handy for intermediate calculations.

SELECT t.pos, t1.pos,
  SUBSTRING(string, t.pos + 1, t1.pos - t.pos -1) AS Result 
FROM [table]
CROSS APPLY ( VALUES(CHARINDEX('.',string)) ) t(pos) 
CROSS APPLY ( VALUES(CHARINDEX('.',string, t.pos+1))) t1(pos)

John Cappelletti · Answer 3 · 2019-07-02T13:25:45.577

Just another option is to use a little XML

Example

Declare @YourTable table (ID int,SomeColumn varchar(max))
Insert Into @YourTable values
 (1,'aaaa.bbb.ccc.dddd.eee.fff.ggg')
,(2,'qq.eeddde.rrr.t.hh.jj.jj.hh.hh')
,(3,'ee.r.t.y.u.i.ii.')


Select ID
      ,SomeValue = convert(xml,'<x>' + replace(SomeColumn,'.','</x><x>')+'</x>').value('/x[2]','varchar(100)')
 From @YourTable

Returns

ID  SomeValue
1   bbb
2   eeddde
3   r

Barbaros Özhan · Answer 4 · 2019-07-02T14:53:04.933

2

You can use left(), replace() and charindex() functions together :

select replace(
        replace(
               left(str,charindex('.',str,charindex('.',str)+1)),
               left(str,charindex('.',str)),
               ''
               ),
               '.'
               ,''
              ) as "Output"
  from t;

Demo

edited Jul 02 '19 at 14:53

answered Jul 02 '19 at 14:05

Barbaros Özhan

59,113
10
31
55

In case I have to find out string between, 2 and 3rd , what should I do ? – Andrew Jul 03 '19 at 00:56
@Andrew consider two step [solution](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=32923911728763b716f062c9351e8a85) in that case. – Barbaros Özhan Jul 03 '19 at 02:52
Thanks Barbaros. Not able to figure this out. I have column name put in str but no luck. – Andrew Jul 05 '19 at 05:41
and what will be best to do in case I have to find sub string between 4 and 5th .. things are getting complicated. I have to update another based on some character are present in the 3rd and 4th (.). in future it might be between 4th and 5th (.). Any other simple solution you have in your mind will be helpful – Andrew Jul 05 '19 at 06:20
1

Hi @Andrew. As an Oracle man I also feel the lack of some regex operations throughout SQL Server. In such type of questions we mostly use `charindex()`which has no `occurence` argument. For this reason, extracting a substring process mostly depends on hard-coding unfortunately. If your strings had two or three dots, you would be able to use `parsename()` function to extract the desired substring easily. – Barbaros Özhan Jul 05 '19 at 06:38

Find Substring in SQL

4 Answers4