Parse A String Using Charindex

Hi,

I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them.
The format of the name field is 'AAAA-BBBBBB-123'
here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id.
I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?


SELECT id AS 'ID',
name AS 'name',
sequence AS 'num'
FROM
FirstTable A
INNER JOIN SecondTable q
ON (CONVERT(INT,RIGHT(name,3))= a.id)
INNER JOIN ThridTable t
ON(t.id = q.id)
INNER JOIN FourthTable s
ON (q.name = s.name )
WHERE A.id = @ID
AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1))
ORDER BY 'ID','num'


One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?

Thanks,
Subha