SQL - Charindex/Substring - How to get 6 characters left of 3rd or last '/' 1

[buckshot323]

I have a field-string in a table that displays like this: Organization/SIS/RND 434444/CIT
All characters could be different in all areas minus the 'Organizaiton/' part and the '/' which will always be three.
For example CIT could be 2 characters, or 3 characters or more.

What I need is to always grab the first 6 characters to the left of the last slash.
So,
Example
Organization/SIS/RND 434444/CIT
End Desired Result
434444

Need to put this into a where statement so I can grab only certain departments which translate to that 6 digits im seeking.

Went through all the forums and pounded google for a while and can't really find anything that works.

Any suggestions from the SQL guru's out there?

 

[Andrzejek]

With

[tt]Organization/SIS/RND [red]434444[/red]/CIT[/tt]

you may want to find last occurrence of a / within a string and then you can use a SUBSTRING function to get the 6 characters before the last /

I would probably add a Computed Column to my table with just this piece of data.


---- Andy

There is a great need for a sarcasm font.

 

[buckshot323]

I have found some code to find the last occurrence of it in the string, but for the life of me I can't figure out the last piece to pull the 6 digits to the left of the '/'. I'm not savvy enough, and that's why I posed this question looking for help. I always end up pulling all data to the right of the string. Then when I reverse the left/right properties of the coding I end up getting some of the text to the left and some of the text to right combined.

So if you could translate your articles into code that would be helpful.

So the string is 'Organization/SIS/RND 434444/CIT'
The field is 'orgpathtxt'.

What is your suggestion with charindex and substring to pull the '434444' only?

Just one line of code would be helpful. Let's just say it's a select statement just to pull those digits from that string.

 

[buckshot323]

So this
right( orgpathtxt, CHARINDEX( '/', REVERSE( orgpathtxt ) + '/' ) + 6),
is giving me the 6 digits plus the slash and all the contents to the right.

So if the string is 'Organization/SIS/RND 434444/CIT'
It's bringing back 434444/CIT

 

[fredericofonseca]

declare @s varchar(50) = 'Organization/SIS/RND 434444/CIT'

this one should always work (assuming that all your strings do indeed have the same format - error handling may be required
select reverse(substring(reverse(@s), charindex('/', reverse(@s)) + 1, 6))

another option - assuming that the chars to the right of the last "/" are 100 or less
select right(stuff(@s,len(@s) - charindex('/', reverse(@s)) + 1, 100, ''), 6)



And if your strings only have a space followed by the 6 numbers followed by a "/" the following is yet another option
select substring(@s, patindex('% [0-9]%/%', @s) + 1, 6)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If this:[tt]
right( orgpathtxt, CHARINDEX( '/', REVERSE( orgpathtxt ) + '/' ) + 6)[/tt]
gives you back this:[tt]
434444/CIT[/tt]

You are almost there.

Try:[tt][blue]
SUBSTR(orgpathtxt, [/blue]right( orgpathtxt, CHARINDEX( '/', REVERSE( orgpathtxt ) + '/' ) + 6)[blue], 6)[/blue][/tt]


---- Andy

There is a great need for a sarcasm font.