none
Select String value in SQL Server RRS feed

  • Question

  • Hi Guys, 
    I have this fields in a SQL Table 

        [header]nombre=LPP Expreso LD Santiago 2 Descargas-01-10-2007 codigo=ACGRZ target=0 activo=0
        [header]nombre=BS.AS / ORAN 01-07-2019 codigo=ACJEX target=0 activo=1 VigenciaDesde=01/07/2019
        [header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10 DEV codigo=ACLGO target=0 activo=1 VigenciaD

    How can i do a query to get the String: codigo=XXXXXX ?
    The text codigo is always in a diferente position 
    Monday, October 21, 2019 4:40 PM

All replies

  • DECLARE @T TABLE (
    	Field varchar(100)
    );
    
    INSERT INTO @T (Field) VALUES
    ('[header]nombre=LPP Expreso LD Santiago 2 Descargas-01-10-2007 codigo=ACGRZ target=0 activo=0'),
    ('[header]nombre=BS.AS / ORAN 01-07-2019 codigo=ACJEX target=0 activo=1 VigenciaDesde=01/07/2019'),
    ('[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10 DEV codigo=ACLGO target=0 activo=1');
    
    SELECT LEFT(SUBSTRING(Field, CHARINDEX('codigo=', Field) + LEN('codigo='), LEN(Field) - LEN('codigo=')), CHARINDEX(' ', SUBSTRING(Field, CHARINDEX('codigo=', Field) + LEN('codigo='), LEN(Field) - LEN('codigo='))))
    FROM @T;


    A Fan of SSIS, SSRS and SSAS


    Monday, October 21, 2019 4:54 PM
  • It seems you want to get the field with the value:

    DECLARE @T TABLE (
    	Field varchar(100)
    );
    
    INSERT INTO @T (Field) VALUES
    ('[header]nombre=LPP Expreso LD Santiago 2 Descargas-01-10-2007 codigo=ACGRZ target=0 activo=0'),
    ('[header]nombre=BS.AS / ORAN 01-07-2019 codigo=ACJEX target=0 activo=1 VigenciaDesde=01/07/2019'),
    ('[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10 DEV codigo=ACLGO target=0 activo=1');
    
    SELECT LEFT(SUBSTRING(Field, CHARINDEX('codigo=', Field), LEN(Field)), CHARINDEX(' ', SUBSTRING(Field, CHARINDEX('codigo=', Field), LEN(Field))))
    FROM @T;


    A Fan of SSIS, SSRS and SSAS

    Monday, October 21, 2019 5:09 PM
  • Create table test  (
    	Col varchar(2000)
    );
    
    INSERT INTO test (col) VALUES
    ('[header]nombre=LPP Expreso LD Santiago 2 Descargas-01-10-2007 codigo=ACGRZ target=0 activo=0'),
    ('[header]nombre=BS.AS / ORAN 01-07-2019 codigo=ACJEX target=0 activo=1 VigenciaDesde=01/07/2019'),
    ('[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10 DEV codigo=ACLGO target=0 activo=1')
    ,('[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10')
    ,(null)
    ,('');
    
     
    
    ;with mycte as (
    SELECT 
    Case when CHARINDEX('codigo=', Col) >0 then  Stuff( Col, 1,CHARINDEX('codigo=', Col)+7,'') else null end newCol
    FROM test)
    
    Select stuff(newCol, Charindex(' ',newcol),len(newcol),'') as mycodigo
    
    from mycte
    
    
    drop table test

    Monday, October 21, 2019 5:19 PM
    Moderator


  • >> I have these fields in a SQL Table <<

    Please read ANY book on SQL before you post. A field is a part of a column. Please learn the ISO format for dates .(yyyy-mm-dd). Please learn to post DDL!!

    Why do you ask people to do your job for you and then spit on them? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, October 21, 2019 6:08 PM
  • Hi Antonioll,

    Please try following script.

    Create table test  (
    	Col varchar(2000)
    );
    
    INSERT INTO test (col) VALUES
    ('[header]nombre=LPP Expreso LD Santiago 2 Descargas-01-10-2007 codigo=ACGRZ target=0 activo=0'),
    ('[header]nombre=BS.AS / ORAN 01-07-2019 codigo=ACJEX target=0 activo=1 VigenciaDesde=01/07/2019'),
    ('[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10 DEV codigo=ACLGO target=0 activo=1')
    ,('[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10')
    ,(null)
    ,('');
    
    
    SELECT 
    Case when CHARINDEX('codigo=', Col) >0 
    then  substring( Col, CHARINDEX('codigo=', Col),CHARINDEX(' ',ltrim(right(Col, len(Col)-CHARINDEX('codigo=', Col))))) else null end newCol
    FROM test
    /*
    newCol
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    codigo=ACGRZ
    codigo=ACJEX
    codigo=ACLGO
    NULL
    NULL
    NULL
    */
    
    
    SELECT 
    Case when CHARINDEX('codigo=', Col) >0 
    then  substring( Col, CHARINDEX('codigo=', Col)+7,CHARINDEX(' ',ltrim(right(Col, len(Col)-CHARINDEX('codigo=', Col)-7)))) else null end newCol
    FROM test
    /*
    newCol
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ACGRZ
    ACJEX
    ACLGO
    NULL
    NULL
    NULL
    */
    drop table test

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 6:21 AM
  • And once more, CELKO message is marked as abusive for a good reason😡


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, October 23, 2019 6:02 AM
    Moderator