stoner
stoner

Reputation: 407

Right Substring Statement SQL

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: 39452

Answers (2)

Bacon Bits
Bacon Bits

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

Lamak
Lamak

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

Related Questions