substring value into variable

• kavali.ongole

  Old Hand

  Points: 320

  More actions

  June 2, 2010 at 12:12 am

  #221692

  Hi

  i am selecting the following dml statement from jobs command column and my requirement is i want to store the sub string value of .0125 into another variable.

  DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .0125)

  please help me, how to store the .0125 substing into variable.

  Thanks

  Kavali

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 2, 2010 at 12:32 am

  #1175118

  Kavali, the request is quite unclear for me.. can you please put some visual samples of your request and its desired results?

  for starters, this might help you!

  DECLARE @SUB VARCHAR(2)

  SELECT @SUB = SUBSTRING(CAST(O.125 AS VARCHAR),1,2)

  SELECT @SUB

• kavali.ongole

  Old Hand

  Points: 320

  More actions

  June 2, 2010 at 1:42 am

  #1175127

  Hi

  select J.name, JS.command, from msdb.dbo.sysjobsteps JS

  join msdb.dbo.sysjobs J on J.job_id=JS.job_id

  where name=@sessionjobname (predefined variable)

  The above query returns command column data as

  DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .0125)

  from this command column data i need to store the .0125 into string variable and .0125 value is not constant it will change depending on the database.

  Please let me know if you want more details.

  Thanks in advance for your help.

  Thanks

  Kavali

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 2, 2010 at 2:30 am

  #1175143

  Ok, i get it.. Here is what you wanted (from what i understoood from your above post)

  First lets build a temp table to hold some data that looks similar to yours

  Sample table and data

  DECLARE @Table TABLE

  (

  COMMANDS VARCHAR(500)

  )

  INSERT INTO @Table (COMMANDS)

  SELECT 'DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .0125)'

  UNION ALL SELECT 'DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .5789)'

  UNION ALL SELECT 'DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .123456)'

  UNION ALL SELECT 'DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .01)'

  UNION ALL SELECT 'DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .100)'

  UNION ALL SELECT 'DELETE FROM SE_MODULE WHERE (SE_MODULE_DATE < GETDATE() - .01)'

  SELECT COMMANDS FROM @Table

  Now for the code taht will get part of the string that you want:

  SELECT LTRIM(REVERSE(SUBSTRING(REVERSE(COMMANDS), 2 , CHARINDEX('-',REVERSE(COMMANDS))- 2))) FROM @Table

  If you want to store the above into a variable, then you must get only one row as resuult of the query you posted;

  Use this code to store the stripped-string into a variable

  DECLARE @POINTDATA VARCHAR(15)

  SELECT @POINTDATA = REVERSE(SUBSTRING(REVERSE(COMMANDS), 2 , CHARINDEX('-',REVERSE(COMMANDS))- 2)) FROM @Table

  SELECT @POINTDATA

  Hope this is what you wanted. Please tell us if the above code fixed your requirement!

  C'est Pras!

• kavali.ongole

  Old Hand

  Points: 320

  More actions

  June 2, 2010 at 4:33 am

  #1175190

  Thanks a lot for your help..

  you scripts help me for my requirement, instead of creating the table directly i am passing the command value into variable from there i am taking the substring.

  Thanks

  Kavali

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 2, 2010 at 4:56 am

  #1175196

  My pleasureh helping you, welcome! 🙂

• Jeff Moden

  SSC Guru

  Points: 1004639

  More actions

  June 2, 2010 at 7:32 am

  #1175273

  kavali.ongole (6/2/2010)

  ---

  Thanks a lot for your help..

  you scripts help me for my requirement, instead of creating the table directly i am passing the command value into variable from there i am taking the substring.

  Thanks

  Kavali

  To go along with what ColdCoffee showed you, I recommend that you read about CREATE PROCEDURE in Books Online (the free help system that comes with SQL Server).

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)