Reputation: 407
I want to substring the seventh and eighth caractere from string by starting on the right
I want to make dynamic this try :
select substring(right(P.Name,8), 1,2)
How do you properly write a Right Substring function SQL? What I am doing is not working.
Upvotes: 4
Views: 39453
Reputation: 32200
This makes more sense to me than the double REVERSE()
method:
select substring(P.Name,len(P.Name) + 1 - 8, 2)
We use length + 1 because substring()
is a 1-based rather than 0-based function. That is, the first character is character 1, not character 0.
Note that if the field is less than 7 characters long, an empty string is returned. If the field is exactly 7 characters long, only the 7th character is returned. If the field is at least 8 characters long, the 7th and 8th characters are returned.
Upvotes: 3
Reputation: 70668
You should look how to properly use SUBSTRING
:
SELECT SUBSTRING('123456789',7,2)
The 7
is the position where you start, and the 2
is the length of the string that you want to retrieve.
EDIT
If you want SUBSTRING
starting from the right (according to your comments), you can use REVERSE
:
SELECT SUBSTRING(REVERSE('123456789'),7,2)
Upvotes: 10