Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting only some text from a larger string
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 12/04/2012 :  16:47:35  Show Profile  Reply with Quote
I am having issue grabbing only a certain part of text from a larger string of text. I am creating a runbook and need to capture a runcontrol name to display in a field using a SQL View in Peoplesoft. I would be able to do this if the stings were all uniform, but they way they are put together can vary.

Here are some examples of the stings that I'm seeing:

1) -process UWBNR054 -type 'SQR Report' -runcontrol BT_BN_UWBNR054_SGL -outputdest RDM_HRS_PROD -outputtype Printer -outputformat HP
2)-process UWPYR007 -type 'SQR Report' -runcontrol BT_PY_UWPYR007
3) /tws/doit/tws_backup

All I want is the run control name. As you can see, some of the strings don't even have a runcontrol. Other times the string ends with the runcontrol name as the last part. And yet other times, there is more text after the runcontol name.

If the runcontol exists in the string, it will always start with "-runcontrol ". It is how the string ends that is throwing me for a loop.

I am trying to find some SQL that will capture only the runcontrol name regardless of if there is a 'space' after it, or if the runcontrol name ends the string. If there is no runcontrol in the string, I would like for the SQL to yeild nothing.

Any help on this would be much appreciated. Thanks in advance.

Aged Yak Warrior

545 Posts

Posted - 12/05/2012 :  15:10:27  Show Profile  Reply with Quote
Nobody answered this, so I'm giving it a try.

Replace the red text with the appropriate fieldname/tablename:

select substring(thefield,charindex('-runcontrol ',thefield)+12,charindex(' ',charindex('-runcontrol ',thefield)+12)-(charindex('-runcontrol ',thefield)+12)) as controlname
  from thetable
 where thefield like '%-runcontrol %'

Edited by - bitsmed on 12/05/2012 15:11:26
Go to Top of Page

Starting Member

2 Posts

Posted - 12/05/2012 :  16:43:50  Show Profile  Reply with Quote
Here is what I ended up with that worked.

select case when instr(jod_task_string,'-runcontrol ') = 0 then ' '
else case when instr(jod_task_string, ' ', instr(jod_task_string,'-runcontrol ')+12) = 0 then substr(jod_task_string,(instr(jod_task_string,'-runcontrol ')+12))
else substr(jod_task_string,(instr(jod_task_string,'-runcontrol ')+12),(instr(jod_task_string,' ',(instr(jod_task_string,'-runcontrol ')+12)))-(instr(jod_task_string,'-runcontrol ')+12)) end end as Run_Control from jod_job_definitions
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000