CODEWITHSUNDEEP
sqlsql-servert-sql
Surya
Surya

Reputation: 51

SQL server Sub string

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

Upvotes: 0

Views: 77

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

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

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 3

Related Questions