Query Help

[Arielle]

Hey folks, I'm working with an already existing database trying to pull information for a new report they want added. In the database there is a COC (Chain of Command) column that lists in order from heighest to lowest the chain of command for that department with their IDs.

Example: 1;29;122;124;1532

What I need to do is find out how their immediate tier 2 contact is based on their department. I want to make this into a stored procedure since there are about five reports I'm doing and four of them require this information. Basically I decided to approach it by extracting the second number listed in the string. (29 in the example) but I'm having some difficulty.

SELECT SUBSTRING(COC, CHARINDEX(';', COC) + 1, CHARINDEX(';', COC, CHARINDEX(';', COC) + 1) - CHARINDEX(';', COC)) AS TopBoss
FROM Departments


That is my happy query that gives me an error. I think it's because where I subtract the two different char indexes it barfs on me. Maybe I need to convert them to integers first?

[iburyak]

Try this:


[PHP]SELECT SUBSTRING(COC, CHARINDEX(';', COC) + 1,
CHARINDEX(';',SUBSTRING(COC, CHARINDEX(';',COC) + 1, 1000)) - 1) AS TopBoss
FROM Departments[/PHP]

[Arielle]

Thanks so much, the nested substring worked perfectly. I had to adjust what you wrote slightly but it still definately put me on the right track.

SELECT     SUBSTRING(COC, CHARINDEX(';', COC) + 1, CHARINDEX(';', SUBSTRING(COC, CHARINDEX(';', COC), 1000))) AS TopBoss
FROM         Departments