CODEWITHSUNDEEP
sqlsql-serversubstring
HaiY
HaiY

Reputation: 195

Substring after a dot in SQL Server

I have a field which holds data like this

Product.Company.Price.Item

I want to substring any string after the last dot. so it will look like this

Item

I know there is a substring.index function in MySQL. How can I implement this in SQL Server?

Upvotes: 0

Views: 4386

Answers (4)

Ilyes
Ilyes

Reputation: 14928

If it's always 4 parts then you could use parsename() as

select parsename('Product.Company.Price.Item', 1)

or use right() and charindex() as

select right(str, p)
from
(
    values ('Product.Company.Price.Item'), ('Other.row')
) t(str) cross apply
(
    values (charindex('.', reverse(str))-1)
) tt(p)

Upvotes: 1

Thiyagu
Thiyagu

Reputation: 1330

Hope this Query works fine for your Case:

DECLARE @VAR VARCHAR(MAX)='Product.Company.Price.Item'

SELECT RIGHT(@VAR, CHARINDEX('.', REVERSE(@VAR) + '.') - 1) AS VARL

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this using functions like Reverse(), Left(), and the CHARINDEX().

DECLARE @Sentence VARCHAR(100) = 'Product.Company.Price.Item';

SELECT REVERSE(LEFT(REVERSE(@Sentence),
CHARINDEX('.',REVERSE(@Sentence))- 1)) AS [Last_Word]

Upvotes: 0

Sparky
Sparky

Reputation: 15075

select  reverse(substring(reverse(fieldName),1,charindex('.',reverse(fieldname))-1))

This will do what you want, but I wouldn't use it as part of a where expression

declare @x varchar(50) = 'Product.Company.Price.Item'
select  substring(@x,len(@x)-charindex('.',reverse(@x))+2,99)

Upvotes: 3

Related Questions