Substring a value after a period

Question

Substring a value after a period

hoseam

SSCertifiable

Points: 5533
More actions
November 11, 2016 at 4:43 am

#322835

I get string values as input, e.g 'Documentation.Account', whatever value I get I want to extract it to get anything after a period(.) e.g'Account' I was thinking of using Substring, but I'm not sure how to go on about it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What have you tried? If you show us that, we can show you where you're going wrong. Here's a hint: use CHARINDEX to find the position of the first "." and SUBSTRING (or RIGHT) to return everything after it. You may also need to use LEN to get the length of the string, and hence the number of characters after the ".".

John

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

hoseam (11/11/2016)
I get string values as input, e.g 'Documentation.Account', whatever value I get I want to extract it to get anything after a period(.) e.g'Account' I was thinking of using Substring, but I'm not sure how to go on about it.

This should get you started

😎

DECLARE @INPUT VARCHAR(50) = 'Documentation.Account';

SELECT

@INPUT AS INPUT_STRING

,SUBSTRING(@INPUT,CHARINDEX(CHAR(46),@INPUT,1) + 1,50) AS SUBSTRING_STRING

,STUFF(@INPUT,1,CHARINDEX(CHAR(46),@INPUT,1),'') AS STUFF_STRING

,RIGHT(@INPUT,CHARINDEX(CHAR(46),REVERSE(@INPUT)) - 1) AS RIGHT_STRING

;

Output

INPUT_STRING SUBSTRING_STRING STUFF_STRING RIGHT_STRING

------------------------ ------------------ -------------- -------------

Documentation.Account Account Account Account

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

hoseam (11/11/2016)
I get string values as input, e.g 'Documentation.Account', whatever value I get I want to extract it to get anything after a period(.) e.g'Account' I was thinking of using Substring, but I'm not sure how to go on about it.

There are some limits but the following will work..

SELECT LeftPart = PARSENAME(somecolumn,2)

,RightPart = PARSENAME(somecolumn,1)

FROM dbo.sometable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

Jeff Moden (11/11/2016)
hoseam (11/11/2016)
I get string values as input, e.g 'Documentation.Account', whatever value I get I want to extract it to get anything after a period(.) e.g'Account' I was thinking of using Substring, but I'm not sure how to go on about it.
There are some limits but the following will work..
SELECT LeftPart = PARSENAME(somecolumn,2)
,RightPart = PARSENAME(somecolumn,1)
FROM dbo.sometable

Not to mention that PARSENAME is roughly 5 times faster than the other methods I posted earlier.

😎