locked
extract string from middle RRS feed

  • Question

  • Guys,

    I have a column with string like below 

    SQLCMD -I -S "EGRDMSQLDEV01.CPS.INTEL.COM\RDMDEV1,3180" -d DF -b -I -i "D:\DF\SSIS\CI\RDMS_SET_CONCAT_NULLS_OFF.SQL " -i "C:\TFS\RDMS\Performance Utilities\DEVADMIN1_1REFRESHTEST\Refresh\CA\PROCS\forecast.PR_ADD_ROLE_MEMBERS.sql" >> \\EGRDMSQLUAT01\DF$\CIBUILDLOG\DEVADMIN11REFRESHTEST\EGRDMSQLDEV01_ADMIN_1_1REFRESHTEST_20130925.txt

    And my task is to extract string from middle of the above mentioned string which is highlighted. And I know that the string which i want to extract starts with C:\TFS ends with .sql.

    Any help is highly appreciated.

    Thanks.

    Friday, October 4, 2013 3:34 AM

Answers

  • Refer the below sql,

    DECLARE @STRS VARCHAR(300)
    SET @STRS = 'SQLCMD -I -S "EGRDMSQLDEV01.CPS.INTEL.COM\RDMDEV1,3180" -d DF -b -I -i "D:\DF\SSIS\CI\RDMS_SET_CONCAT_NULLS_OFF.SQL " -i "C:\TFS\RDMS\Performance Utilities\DEVADMIN1_1REFRESHTEST\Refresh\CA\PROCS\forecast.PR_ADD_ROLE_MEMBERS.sql" >> \\EGRDMSQLUAT01\DF$\CIBUILDLOG\DEVADMIN11REFRESHTEST\EGRDMSQLDEV01_ADMIN_1_1REFRESHTEST_20130925.txt'
    SELECT SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),
    CHARINDEX('.SQL', SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),1) [.SQL POSITION],
    SUBSTRING(SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),1,CHARINDEX('.SQL', SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),1) +3) [FINAL STRING]


    Regards, RSingh

    • Proposed as answer by Saeid Hasani Friday, October 4, 2013 7:14 AM
    • Marked as answer by Murali dhar Friday, October 4, 2013 4:32 PM
    Friday, October 4, 2013 3:51 AM

All replies

  • Hello professionals, Below is my table script 

    CREATE TABLE MURALI
    (
    VALUE nVARCHAR(500) ,
    ID BIGINT IDENTITY(1,1)
    
    )
    
    
    
    INSERT INTO [MURALI].[dbo].[MURALI]
               ([VALUE])
         VALUES
               ('SQLCMD -I -S "EGRDMSQLDEV01.CPS.INTEL.COM\RDMDEV1,3180" -d DF -b -I -i "D:\DF\SSIS\CI\RDMS_SET_CONCAT_NULLS_OFF.SQL " -i "C:\TFS\RDMS\Performance Utilities\DEVADMIN1_1REFRESHTEST\Refresh\CA\PROCS\forecast.PR_ADD_ROLE_MEMBERS.sql" >> \\EGRDMSQLUAT01\DF$\CIBUILDLOG\DEVADMIN11REFRESHTEST\EGRDMSQLDEV01_ADMIN_1_1REFRESHTEST_20130925.txt')
    GO
    

    I want to populate below string value from the above  full string

    C:\TFS\RDMS\Performance Utilities\DEVADMIN1_1REFRESHTEST\Refresh\CA\PROCS\forecast.PR_ADD_ROLE_MEMBERS.sql

    Thanks much guys.

    Friday, October 4, 2013 12:49 AM
  • You created Table "MURALI" with default Schema (dbo). But you insert data in murali.dbo.murali? do you write this code within a database "murali"?

    If the answer is NO, I guess you have a simple syntax error. you can try this code instead:

    INSERT INTO [dbo].[MURALI]
               ([VALUE])
         VALUES
               ('SQLCMD -I -S "EGRDMSQLDEV01.CPS.INTEL.COM\RDMDEV1,3180" -d DF -b -I -i "D:\DF\SSIS\CI\RDMS_SET_CONCAT_NULLS_OFF.SQL " -i "C:\TFS\RDMS\Performance Utilities\DEVADMIN1_1REFRESHTEST\Refresh\CA\PROCS\forecast.PR_ADD_ROLE_MEMBERS.sql" >> \\EGRDMSQLUAT01\DF$\CIBUILDLOG\DEVADMIN11REFRESHTEST\EGRDMSQLDEV01_ADMIN_1_1REFRESHTEST_20130925.txt')
    


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Friday, October 4, 2013 12:57 AM
  • yes database name is murali.
    Friday, October 4, 2013 12:58 AM
  • What are the rules?

    Which parts of string are constant? 


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Friday, October 4, 2013 1:04 AM
  • hi , 

    that is total string in a column, I want to extract data from middle of string. that is just the code and some path. Nothing is constant. 

    Friday, October 4, 2013 1:09 AM
  • thanks for you help. 

    i found a way. 

    SELECT SUBSTRING(value, CHARINDEX(' " -i "', value) + 5, 110) from murali

    Friday, October 4, 2013 1:13 AM
  • But i guess i need dynamic :)
    Friday, October 4, 2013 1:15 AM
  • So you found your rule and the constant part.

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Friday, October 4, 2013 1:15 AM
  • What do you mean by "dynamic"?

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012


    Friday, October 4, 2013 1:20 AM
  • Sorry , i was not clear about that. my constant  is 'c:\tfs'
    Friday, October 4, 2013 1:27 AM
  • SELECT SUBSTRING(value, CHARINDEX('C:', value),+ CHARINDEX('.SQL',VALUE)-6) from murali
    i'm now a step ahead..
    Friday, October 4, 2013 1:45 AM
  • Refer the below sql,

    DECLARE @STRS VARCHAR(300)
    SET @STRS = 'SQLCMD -I -S "EGRDMSQLDEV01.CPS.INTEL.COM\RDMDEV1,3180" -d DF -b -I -i "D:\DF\SSIS\CI\RDMS_SET_CONCAT_NULLS_OFF.SQL " -i "C:\TFS\RDMS\Performance Utilities\DEVADMIN1_1REFRESHTEST\Refresh\CA\PROCS\forecast.PR_ADD_ROLE_MEMBERS.sql" >> \\EGRDMSQLUAT01\DF$\CIBUILDLOG\DEVADMIN11REFRESHTEST\EGRDMSQLDEV01_ADMIN_1_1REFRESHTEST_20130925.txt'
    SELECT SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),
    CHARINDEX('.SQL', SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),1) [.SQL POSITION],
    SUBSTRING(SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),1,CHARINDEX('.SQL', SUBSTRING(@STRS,CHARINDEX('C:\TFS',@STRS,1),LEN(@STRS)),1) +3) [FINAL STRING]


    Regards, RSingh

    • Proposed as answer by Saeid Hasani Friday, October 4, 2013 7:14 AM
    • Marked as answer by Murali dhar Friday, October 4, 2013 4:32 PM
    Friday, October 4, 2013 3:51 AM
  • I saw your another question in this thread:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c140229a-12df-4fbc-bf41-4c8c96efed98/extract-string-from-middle?forum=transactsql

    I am happy now because you could find your rules. Now I guess you have your solution. If there is still problems, let me know.


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Friday, October 4, 2013 7:21 AM
  • Thanks sir
    Friday, October 4, 2013 4:34 PM