SQL server Sub string

Question

could you please help in getting the below sub string.

I have values like

1.1
1.10.1
1.2.2.1

expected output (need to exclude the digits after the second dot)

1.1
1.10
1.2

SELECT LEFT(yourstring, CHARINDEX('.', yourstring, CHARINDEX('.', yourstring)+1)-1); — AreEl
– AreEl, Commented Jan 16, 2020 at 15:01

John Cappelletti · Accepted Answer · 2020-01-16 14:58:09Z

3

Here is one approach that demonstrates the use of a CROSS APPLY and a little XML

Example

Declare @YourTable Table ([SomeCol] varchar(50))  Insert Into @YourTable Values 
 ('1.1')
,('1.10.1')
,('1.2.2.1')

Select A.* 
      ,NewValue=concat(
               XMLData.value('/x[1]','varchar(50)')
               ,'.'+XMLData.value('/x[2]','varchar(50)')
               )
 From  @YourTable A
 Cross Apply (  values (Cast('<x>' + replace(SomeCol,'.','</x><x>')+'</x>' as xml)) ) B(XMLData)

Returns

SomeCol   NewValue
1.1       1.1
1.10.1    1.10
1.2.2.1   1.2

EDIT - Just another option using parsename()

Select A.* 
      ,NewValue=reverse(parsename(reverse(SomeCol),2)
              +'.'
              +parsename(reverse(SomeCol),1)
              )
From  @YourTable A

answered Jan 16, 2020 at 14:58

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AreEl Over a year ago

SELECT LEFT(yourstring, CHARINDEX('.', yourstring, CHARINDEX('.', yourstring)+1)-1);

Surya Over a year ago

Perfect, Thank you very much

JNevill Over a year ago

reverse... that is a sneaky way of shoehorning parsename() into this. I love it. I was trying to make STRING_SPLIT() and STRING_AGG() to work here, but there is no way to guarantee order in the output which is a HUGE disappointment.

Turo Over a year ago

@JNevill Ups, i answered it this way, in SqlFiddle it looked fine...

John Cappelletti Over a year ago

@Turo string_split() may work 99.9999% of the time with a small sample, but there is no GTD of the sequence. This is a tragic omission from string_split().

Yogesh Sharma · Accepted Answer · 2020-01-16 15:06:54Z

0

Simplest appraoch is to use with combination of LEFT() & CHARINDEX() :

SELECT LEFT(col, CHARINDEX('.', col + '.', CHARINDEX('.', col + '.') + 1 ) - 1)
FROM table t;

edited Jan 16, 2020 at 15:06

answered Jan 16, 2020 at 15:03

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

5 Comments

apomene Over a year ago

this wont work for 1st string (1.1) where you have olny 1 occurence of .

Surya Over a year ago

LEFT(TV_AB_Name, case when CHARINDEX('.', TV_AB_Name, CHARINDEX('.', TV_AB_Name)+1) = 0 then len(TV_AB_Name) else CHARINDEX('.', TV_AB_Name, CHARINDEX('.', TV_AB_Name)+1) - 1 end )

Surya Over a year ago

please review and confirm

Yogesh Sharma Over a year ago

@Surya. . You don't need case expression for this. Use LEFT(TV_AB_Name, CHARINDEX('.', TV_AB_Name + '.', CHARINDEX('.', TV_AB_Name + '.') + 1 ) - 1)

Surya Over a year ago

For my requirement the logic without case is not working. the one with case which i sent is working fine. Thank you very much

Collectives™ on Stack Overflow

SQL server Sub string

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related