SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

K_double_U
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.

bitsmed
Posting Yak Master

Denmark
100 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

K_double_U
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  
 New 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.06 seconds. Powered By: Snitz Forums 2000