×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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?

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

With

Organization/SIS/RND 434444/CIT

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.

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

(OP)
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.

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

(OP)
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

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

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

If this:
right( orgpathtxt, CHARINDEX( '/', REVERSE( orgpathtxt ) + '/' ) + 6)

gives you back this:
434444/CIT


You are almost there. smile

Try:
SUBSTR(orgpathtxt,
right( orgpathtxt, CHARINDEX( '/', REVERSE( orgpathtxt ) + '/' ) + 6), 6)


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close