getting substring to start from right?

[jmj]

I have a concatonated field that holds 4 values.
SchoolID+StudentID+Grade+SchoolYear AS
I need to get extract out one value (grade). Originally I did this:
CAST(SUBSTRING(NewIdentity, 8,2)AS VARCHAR)
Worked ok until I realized that some of the studentids vary in length between 1 to 4.
So now I'm wondering can I get the data from the right? I know that the last 2 parts of this value will always use 7 character spaces.
Coming from the left there can be anywhere between 4-7 character spaces.
Make sense? Seems like it should be easy to do?
thanks,
j

 

[boflexson]

REVERSE(CAST(SUBSTRING(REVERSE(NewIdentity), 5,2)AS VARCHAR))

If the Year is 4 digits, this should work.
-bo

-Adam T. Courtney

 

[SQLSister]

First, now you know why you should NEVER, EVER store more than one piece of information in a field. This is the first rule of database design. Very poor database design.

That said, you would figure the len of the whole string in the field- 7 as the end point of your substring. I'm assuming NewIdentity is your field name.

CAST(SUBSTRING(NewIdentity, 8,(len(NewIdentity)-7) AS VARCHAR)

There is no way this will ever be an efficient structure when your queries have to do this for every record. So you really need to redesign.

 

[jmj]

Thanks to both of you. I added the field to the table and then I used the reverse(case...) to populate it.
We made the concatonated field thinking it would only be used for archived info only. (so the database would not have to check 4 fields for each record.
However, I had all the fields that make up the id broken back out into individual fields.
Thanks again

 

[clarkin]

Well you appear to have solved it, but what was an example of one of the concatenated strings? It is possible you could do some pattern matching with PATINDEX().

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].