Selecting only some text from a larger string

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting only some text from a larger string

Author

Topic

K_double_U
Starting Member

2 Posts

Posted - 2012-12-04 : 16:47:35

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
Aged Yak Warrior

545 Posts

Posted - 2012-12-05 : 15:10:27

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 %'

K_double_U
Starting Member

2 Posts

Posted - 2012-12-05 : 16:43:50

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources