SQL Server : find value inside a string

Multi tool use
Multi tool use












-1















I'm using:




  • Microsoft SQL Server 2016 (SP1) (X64) on Windows Server 2012 R2

  • Microsoft SQL Server Management Studio 14.0.17213.0


I have the following values in a table:



table new_value from DB usm_system_change_detail



I need a query that finds the value for txtf_dns_name= in column 'DNS_texto' and returns only the result of it. In the example it would only be "First_Domain" and "Second_Domain"



After using the tips of colleagues, I was able to do partially:



declare @var varchar(max) = (select new_value
from usm_system_change_detail AS uscd
inner join usm_system_change AS usc on uscd.id = usc.id
inner join usm_request ON usc.object_id = usm_request.request_id
where object_id = '14170'
and new_value like '%silo01.mysql.bdh%'
and nsp_path= 'REQ_CREATE:ADDED')

select
substring(substring(@var, charindex('txtf_dns_nome', @var) + 14, 99), 1, charindex(',', substring(@var, charindex('txtf_dns_nome', @var) + 14, 99)) - 1)


This returns:



silo01.mysql.bdh


But if I remove the where clause, I get this:




Mensagem 245, Nível 16, Estado 1, Linha 1

Conversion failed when converting the varchar value 'f8543002' to data type int.











share|improve this question




















  • 2





    What version of SQL server? Please also post the SQL you tried.

    – Larnu
    Jan 2 at 15:55








  • 1





    What version of sql-server are you using?

    – Ryan Wilson
    Jan 2 at 15:55






  • 4





    Also... I think there is an erratum in the string you posted as the value txtf_dns_name is not in your example

    – Angel M.
    Jan 2 at 15:58






  • 1





    Building off of Angel M. in your example string it is txtf_dns_nome= not txtf_dns_name=

    – Ryan Wilson
    Jan 2 at 15:59













  • Please post your attempt to use substring, and what error you got.

    – Tab Alleman
    Jan 2 at 16:29
















-1















I'm using:




  • Microsoft SQL Server 2016 (SP1) (X64) on Windows Server 2012 R2

  • Microsoft SQL Server Management Studio 14.0.17213.0


I have the following values in a table:



table new_value from DB usm_system_change_detail



I need a query that finds the value for txtf_dns_name= in column 'DNS_texto' and returns only the result of it. In the example it would only be "First_Domain" and "Second_Domain"



After using the tips of colleagues, I was able to do partially:



declare @var varchar(max) = (select new_value
from usm_system_change_detail AS uscd
inner join usm_system_change AS usc on uscd.id = usc.id
inner join usm_request ON usc.object_id = usm_request.request_id
where object_id = '14170'
and new_value like '%silo01.mysql.bdh%'
and nsp_path= 'REQ_CREATE:ADDED')

select
substring(substring(@var, charindex('txtf_dns_nome', @var) + 14, 99), 1, charindex(',', substring(@var, charindex('txtf_dns_nome', @var) + 14, 99)) - 1)


This returns:



silo01.mysql.bdh


But if I remove the where clause, I get this:




Mensagem 245, Nível 16, Estado 1, Linha 1

Conversion failed when converting the varchar value 'f8543002' to data type int.











share|improve this question




















  • 2





    What version of SQL server? Please also post the SQL you tried.

    – Larnu
    Jan 2 at 15:55








  • 1





    What version of sql-server are you using?

    – Ryan Wilson
    Jan 2 at 15:55






  • 4





    Also... I think there is an erratum in the string you posted as the value txtf_dns_name is not in your example

    – Angel M.
    Jan 2 at 15:58






  • 1





    Building off of Angel M. in your example string it is txtf_dns_nome= not txtf_dns_name=

    – Ryan Wilson
    Jan 2 at 15:59













  • Please post your attempt to use substring, and what error you got.

    – Tab Alleman
    Jan 2 at 16:29














-1












-1








-1








I'm using:




  • Microsoft SQL Server 2016 (SP1) (X64) on Windows Server 2012 R2

  • Microsoft SQL Server Management Studio 14.0.17213.0


I have the following values in a table:



table new_value from DB usm_system_change_detail



I need a query that finds the value for txtf_dns_name= in column 'DNS_texto' and returns only the result of it. In the example it would only be "First_Domain" and "Second_Domain"



After using the tips of colleagues, I was able to do partially:



declare @var varchar(max) = (select new_value
from usm_system_change_detail AS uscd
inner join usm_system_change AS usc on uscd.id = usc.id
inner join usm_request ON usc.object_id = usm_request.request_id
where object_id = '14170'
and new_value like '%silo01.mysql.bdh%'
and nsp_path= 'REQ_CREATE:ADDED')

select
substring(substring(@var, charindex('txtf_dns_nome', @var) + 14, 99), 1, charindex(',', substring(@var, charindex('txtf_dns_nome', @var) + 14, 99)) - 1)


This returns:



silo01.mysql.bdh


But if I remove the where clause, I get this:




Mensagem 245, Nível 16, Estado 1, Linha 1

Conversion failed when converting the varchar value 'f8543002' to data type int.











share|improve this question
















I'm using:




  • Microsoft SQL Server 2016 (SP1) (X64) on Windows Server 2012 R2

  • Microsoft SQL Server Management Studio 14.0.17213.0


I have the following values in a table:



table new_value from DB usm_system_change_detail



I need a query that finds the value for txtf_dns_name= in column 'DNS_texto' and returns only the result of it. In the example it would only be "First_Domain" and "Second_Domain"



After using the tips of colleagues, I was able to do partially:



declare @var varchar(max) = (select new_value
from usm_system_change_detail AS uscd
inner join usm_system_change AS usc on uscd.id = usc.id
inner join usm_request ON usc.object_id = usm_request.request_id
where object_id = '14170'
and new_value like '%silo01.mysql.bdh%'
and nsp_path= 'REQ_CREATE:ADDED')

select
substring(substring(@var, charindex('txtf_dns_nome', @var) + 14, 99), 1, charindex(',', substring(@var, charindex('txtf_dns_nome', @var) + 14, 99)) - 1)


This returns:



silo01.mysql.bdh


But if I remove the where clause, I get this:




Mensagem 245, Nível 16, Estado 1, Linha 1

Conversion failed when converting the varchar value 'f8543002' to data type int.








sql-server






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 7 at 21:10









marc_s

581k13011221268




581k13011221268










asked Jan 2 at 15:51









user3703942user3703942

13




13








  • 2





    What version of SQL server? Please also post the SQL you tried.

    – Larnu
    Jan 2 at 15:55








  • 1





    What version of sql-server are you using?

    – Ryan Wilson
    Jan 2 at 15:55






  • 4





    Also... I think there is an erratum in the string you posted as the value txtf_dns_name is not in your example

    – Angel M.
    Jan 2 at 15:58






  • 1





    Building off of Angel M. in your example string it is txtf_dns_nome= not txtf_dns_name=

    – Ryan Wilson
    Jan 2 at 15:59













  • Please post your attempt to use substring, and what error you got.

    – Tab Alleman
    Jan 2 at 16:29














  • 2





    What version of SQL server? Please also post the SQL you tried.

    – Larnu
    Jan 2 at 15:55








  • 1





    What version of sql-server are you using?

    – Ryan Wilson
    Jan 2 at 15:55






  • 4





    Also... I think there is an erratum in the string you posted as the value txtf_dns_name is not in your example

    – Angel M.
    Jan 2 at 15:58






  • 1





    Building off of Angel M. in your example string it is txtf_dns_nome= not txtf_dns_name=

    – Ryan Wilson
    Jan 2 at 15:59













  • Please post your attempt to use substring, and what error you got.

    – Tab Alleman
    Jan 2 at 16:29








2




2





What version of SQL server? Please also post the SQL you tried.

– Larnu
Jan 2 at 15:55







What version of SQL server? Please also post the SQL you tried.

– Larnu
Jan 2 at 15:55






1




1





What version of sql-server are you using?

– Ryan Wilson
Jan 2 at 15:55





What version of sql-server are you using?

– Ryan Wilson
Jan 2 at 15:55




4




4





Also... I think there is an erratum in the string you posted as the value txtf_dns_name is not in your example

– Angel M.
Jan 2 at 15:58





Also... I think there is an erratum in the string you posted as the value txtf_dns_name is not in your example

– Angel M.
Jan 2 at 15:58




1




1





Building off of Angel M. in your example string it is txtf_dns_nome= not txtf_dns_name=

– Ryan Wilson
Jan 2 at 15:59







Building off of Angel M. in your example string it is txtf_dns_nome= not txtf_dns_name=

– Ryan Wilson
Jan 2 at 15:59















Please post your attempt to use substring, and what error you got.

– Tab Alleman
Jan 2 at 16:29





Please post your attempt to use substring, and what error you got.

– Tab Alleman
Jan 2 at 16:29












3 Answers
3






active

oldest

votes


















0














You can use a nested substring. As the comments stated, txtf_dns_nome was in the string, not txtf_dns_name



declare @var varchar(max) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'

select substring(substring(@var,charindex('txtf_dns_nome',@var) + 14, 99),1,charindex(',',substring(@var,charindex('txtf_dns_nome',@var) + 14, 99)) - 1)


This basically gets the substring from txtf_dns_nome to the end of the string, and then from that string, gets the substring to the first comma, which leaves you with your value.






share|improve this answer































    0














    Declare @Pattern as NVARCHAR(100);
    SET @Pattern = 'txtf_contact_name=';

    select
    SUBSTRING(MyValue, CHARINDEX(@Pattern, MyValue) + LEN(@Pattern), CHARINDEX('=', MyValue, CHARINDEX(@Pattern, MyValue))) as R
    from
    Table_2
    where
    MyValue like '%' + @Pattern + '%'





    share|improve this answer































      0














      Here is a possible solution using REPLACE, SUBSTRING, LEN and CHARINDEX:



      --The test string
      DECLARE @testString varchar(MAX) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'
      --Local string variable to hold the reduced string
      --This gets the substring starting at the character index of the string you are
      --looking for a value of (txtf_dns_nome)
      --Then using REPLACE we get rid of the value txtf_dns_nome= which leaves a substring
      --Holding the value you want or in case it is not the last key/value, the value
      --Plus the remaining string
      DECLARE @reducedString varchar(MAX) = REPLACE(SUBSTRING(@testString, CHARINDEX('txtf_dns_nome=', @testString), LEN(@testString) - CHARINDEX('txtf_dns_nome', @testString)), 'txtf_dns_nome=', '')
      --Check to see if there is a comma, if so we want to grab the value up to the comma
      --else the searched for string was the last key/value in the string so just get
      --what is left
      SELECT CASE WHEN CHARINDEX(',', @reducedString) = 0 THEN @reducedString ELSE SUBSTRING(@reducedString, 0, CHARINDEX(',', @reducedString, 0)) END


      If you need this as a table solution just do the following, I split it up into an example above to try to explain what is happening and to give you an example for testing.



      TABLE Solution:



      SELECT CASE WHEN CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')) = 0 THEN
      REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')
      ELSE SUBSTRING(REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0, CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0))
      END
      FROM [dbo].[YourTable]


      Possibly Better Table Solution as this doesn't use as many calls to the functions
      and reduces the string values in the sub-query one time each:



      SELECT CASE WHEN CHARINDEX(',', x.[YourColumn]) = 0 THEN x.[YourColumn] ELSE SUBSTRING(x.[YourColumn], 0, CHARINDEX(',', x.[YourColumn], 0)) END AS [YourColumn]
      FROM
      (
      SELECT REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '') AS [YourColumn]
      FROM [dbo].[YourTable]
      ) AS x





      share|improve this answer


























      • After using the tips of colleagues, I was able to do partially:

        – user3703942
        Jan 7 at 17:10











      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009284%2fsql-server-find-value-inside-a-string%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      You can use a nested substring. As the comments stated, txtf_dns_nome was in the string, not txtf_dns_name



      declare @var varchar(max) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'

      select substring(substring(@var,charindex('txtf_dns_nome',@var) + 14, 99),1,charindex(',',substring(@var,charindex('txtf_dns_nome',@var) + 14, 99)) - 1)


      This basically gets the substring from txtf_dns_nome to the end of the string, and then from that string, gets the substring to the first comma, which leaves you with your value.






      share|improve this answer




























        0














        You can use a nested substring. As the comments stated, txtf_dns_nome was in the string, not txtf_dns_name



        declare @var varchar(max) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'

        select substring(substring(@var,charindex('txtf_dns_nome',@var) + 14, 99),1,charindex(',',substring(@var,charindex('txtf_dns_nome',@var) + 14, 99)) - 1)


        This basically gets the substring from txtf_dns_nome to the end of the string, and then from that string, gets the substring to the first comma, which leaves you with your value.






        share|improve this answer


























          0












          0








          0







          You can use a nested substring. As the comments stated, txtf_dns_nome was in the string, not txtf_dns_name



          declare @var varchar(max) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'

          select substring(substring(@var,charindex('txtf_dns_nome',@var) + 14, 99),1,charindex(',',substring(@var,charindex('txtf_dns_nome',@var) + 14, 99)) - 1)


          This basically gets the substring from txtf_dns_nome to the end of the string, and then from that string, gets the substring to the first comma, which leaves you with your value.






          share|improve this answer













          You can use a nested substring. As the comments stated, txtf_dns_nome was in the string, not txtf_dns_name



          declare @var varchar(max) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'

          select substring(substring(@var,charindex('txtf_dns_nome',@var) + 14, 99),1,charindex(',',substring(@var,charindex('txtf_dns_nome',@var) + 14, 99)) - 1)


          This basically gets the substring from txtf_dns_nome to the end of the string, and then from that string, gets the substring to the first comma, which leaves you with your value.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Jan 2 at 16:02









          scsimonscsimon

          22.2k51637




          22.2k51637

























              0














              Declare @Pattern as NVARCHAR(100);
              SET @Pattern = 'txtf_contact_name=';

              select
              SUBSTRING(MyValue, CHARINDEX(@Pattern, MyValue) + LEN(@Pattern), CHARINDEX('=', MyValue, CHARINDEX(@Pattern, MyValue))) as R
              from
              Table_2
              where
              MyValue like '%' + @Pattern + '%'





              share|improve this answer




























                0














                Declare @Pattern as NVARCHAR(100);
                SET @Pattern = 'txtf_contact_name=';

                select
                SUBSTRING(MyValue, CHARINDEX(@Pattern, MyValue) + LEN(@Pattern), CHARINDEX('=', MyValue, CHARINDEX(@Pattern, MyValue))) as R
                from
                Table_2
                where
                MyValue like '%' + @Pattern + '%'





                share|improve this answer


























                  0












                  0








                  0







                  Declare @Pattern as NVARCHAR(100);
                  SET @Pattern = 'txtf_contact_name=';

                  select
                  SUBSTRING(MyValue, CHARINDEX(@Pattern, MyValue) + LEN(@Pattern), CHARINDEX('=', MyValue, CHARINDEX(@Pattern, MyValue))) as R
                  from
                  Table_2
                  where
                  MyValue like '%' + @Pattern + '%'





                  share|improve this answer













                  Declare @Pattern as NVARCHAR(100);
                  SET @Pattern = 'txtf_contact_name=';

                  select
                  SUBSTRING(MyValue, CHARINDEX(@Pattern, MyValue) + LEN(@Pattern), CHARINDEX('=', MyValue, CHARINDEX(@Pattern, MyValue))) as R
                  from
                  Table_2
                  where
                  MyValue like '%' + @Pattern + '%'






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Jan 2 at 16:05









                  ZamZam

                  2,41911129




                  2,41911129























                      0














                      Here is a possible solution using REPLACE, SUBSTRING, LEN and CHARINDEX:



                      --The test string
                      DECLARE @testString varchar(MAX) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'
                      --Local string variable to hold the reduced string
                      --This gets the substring starting at the character index of the string you are
                      --looking for a value of (txtf_dns_nome)
                      --Then using REPLACE we get rid of the value txtf_dns_nome= which leaves a substring
                      --Holding the value you want or in case it is not the last key/value, the value
                      --Plus the remaining string
                      DECLARE @reducedString varchar(MAX) = REPLACE(SUBSTRING(@testString, CHARINDEX('txtf_dns_nome=', @testString), LEN(@testString) - CHARINDEX('txtf_dns_nome', @testString)), 'txtf_dns_nome=', '')
                      --Check to see if there is a comma, if so we want to grab the value up to the comma
                      --else the searched for string was the last key/value in the string so just get
                      --what is left
                      SELECT CASE WHEN CHARINDEX(',', @reducedString) = 0 THEN @reducedString ELSE SUBSTRING(@reducedString, 0, CHARINDEX(',', @reducedString, 0)) END


                      If you need this as a table solution just do the following, I split it up into an example above to try to explain what is happening and to give you an example for testing.



                      TABLE Solution:



                      SELECT CASE WHEN CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')) = 0 THEN
                      REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')
                      ELSE SUBSTRING(REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0, CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0))
                      END
                      FROM [dbo].[YourTable]


                      Possibly Better Table Solution as this doesn't use as many calls to the functions
                      and reduces the string values in the sub-query one time each:



                      SELECT CASE WHEN CHARINDEX(',', x.[YourColumn]) = 0 THEN x.[YourColumn] ELSE SUBSTRING(x.[YourColumn], 0, CHARINDEX(',', x.[YourColumn], 0)) END AS [YourColumn]
                      FROM
                      (
                      SELECT REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '') AS [YourColumn]
                      FROM [dbo].[YourTable]
                      ) AS x





                      share|improve this answer


























                      • After using the tips of colleagues, I was able to do partially:

                        – user3703942
                        Jan 7 at 17:10
















                      0














                      Here is a possible solution using REPLACE, SUBSTRING, LEN and CHARINDEX:



                      --The test string
                      DECLARE @testString varchar(MAX) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'
                      --Local string variable to hold the reduced string
                      --This gets the substring starting at the character index of the string you are
                      --looking for a value of (txtf_dns_nome)
                      --Then using REPLACE we get rid of the value txtf_dns_nome= which leaves a substring
                      --Holding the value you want or in case it is not the last key/value, the value
                      --Plus the remaining string
                      DECLARE @reducedString varchar(MAX) = REPLACE(SUBSTRING(@testString, CHARINDEX('txtf_dns_nome=', @testString), LEN(@testString) - CHARINDEX('txtf_dns_nome', @testString)), 'txtf_dns_nome=', '')
                      --Check to see if there is a comma, if so we want to grab the value up to the comma
                      --else the searched for string was the last key/value in the string so just get
                      --what is left
                      SELECT CASE WHEN CHARINDEX(',', @reducedString) = 0 THEN @reducedString ELSE SUBSTRING(@reducedString, 0, CHARINDEX(',', @reducedString, 0)) END


                      If you need this as a table solution just do the following, I split it up into an example above to try to explain what is happening and to give you an example for testing.



                      TABLE Solution:



                      SELECT CASE WHEN CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')) = 0 THEN
                      REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')
                      ELSE SUBSTRING(REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0, CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0))
                      END
                      FROM [dbo].[YourTable]


                      Possibly Better Table Solution as this doesn't use as many calls to the functions
                      and reduces the string values in the sub-query one time each:



                      SELECT CASE WHEN CHARINDEX(',', x.[YourColumn]) = 0 THEN x.[YourColumn] ELSE SUBSTRING(x.[YourColumn], 0, CHARINDEX(',', x.[YourColumn], 0)) END AS [YourColumn]
                      FROM
                      (
                      SELECT REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '') AS [YourColumn]
                      FROM [dbo].[YourTable]
                      ) AS x





                      share|improve this answer


























                      • After using the tips of colleagues, I was able to do partially:

                        – user3703942
                        Jan 7 at 17:10














                      0












                      0








                      0







                      Here is a possible solution using REPLACE, SUBSTRING, LEN and CHARINDEX:



                      --The test string
                      DECLARE @testString varchar(MAX) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'
                      --Local string variable to hold the reduced string
                      --This gets the substring starting at the character index of the string you are
                      --looking for a value of (txtf_dns_nome)
                      --Then using REPLACE we get rid of the value txtf_dns_nome= which leaves a substring
                      --Holding the value you want or in case it is not the last key/value, the value
                      --Plus the remaining string
                      DECLARE @reducedString varchar(MAX) = REPLACE(SUBSTRING(@testString, CHARINDEX('txtf_dns_nome=', @testString), LEN(@testString) - CHARINDEX('txtf_dns_nome', @testString)), 'txtf_dns_nome=', '')
                      --Check to see if there is a comma, if so we want to grab the value up to the comma
                      --else the searched for string was the last key/value in the string so just get
                      --what is left
                      SELECT CASE WHEN CHARINDEX(',', @reducedString) = 0 THEN @reducedString ELSE SUBSTRING(@reducedString, 0, CHARINDEX(',', @reducedString, 0)) END


                      If you need this as a table solution just do the following, I split it up into an example above to try to explain what is happening and to give you an example for testing.



                      TABLE Solution:



                      SELECT CASE WHEN CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')) = 0 THEN
                      REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')
                      ELSE SUBSTRING(REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0, CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0))
                      END
                      FROM [dbo].[YourTable]


                      Possibly Better Table Solution as this doesn't use as many calls to the functions
                      and reduces the string values in the sub-query one time each:



                      SELECT CASE WHEN CHARINDEX(',', x.[YourColumn]) = 0 THEN x.[YourColumn] ELSE SUBSTRING(x.[YourColumn], 0, CHARINDEX(',', x.[YourColumn], 0)) END AS [YourColumn]
                      FROM
                      (
                      SELECT REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '') AS [YourColumn]
                      FROM [dbo].[YourTable]
                      ) AS x





                      share|improve this answer















                      Here is a possible solution using REPLACE, SUBSTRING, LEN and CHARINDEX:



                      --The test string
                      DECLARE @testString varchar(MAX) = '**chb_cname=,chb_ipv4=value,chb_ipv6=value,lbl_dominio=,sel_dns_zona=dominio.com.br.,table_single_records-sel_rec_single_reverso--1=Não,table_single_records-txtf_rec_single_valor--1=1.1.1.1,txta_dns_motivo=teste,txtf_dns_nome=im_am_a_legend,txtf_contact_ext=1,txtf_contact_matricula=f123456,txtf_contact_name=john@email.com**'
                      --Local string variable to hold the reduced string
                      --This gets the substring starting at the character index of the string you are
                      --looking for a value of (txtf_dns_nome)
                      --Then using REPLACE we get rid of the value txtf_dns_nome= which leaves a substring
                      --Holding the value you want or in case it is not the last key/value, the value
                      --Plus the remaining string
                      DECLARE @reducedString varchar(MAX) = REPLACE(SUBSTRING(@testString, CHARINDEX('txtf_dns_nome=', @testString), LEN(@testString) - CHARINDEX('txtf_dns_nome', @testString)), 'txtf_dns_nome=', '')
                      --Check to see if there is a comma, if so we want to grab the value up to the comma
                      --else the searched for string was the last key/value in the string so just get
                      --what is left
                      SELECT CASE WHEN CHARINDEX(',', @reducedString) = 0 THEN @reducedString ELSE SUBSTRING(@reducedString, 0, CHARINDEX(',', @reducedString, 0)) END


                      If you need this as a table solution just do the following, I split it up into an example above to try to explain what is happening and to give you an example for testing.



                      TABLE Solution:



                      SELECT CASE WHEN CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')) = 0 THEN
                      REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '')
                      ELSE SUBSTRING(REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0, CHARINDEX(',', REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', ''), 0))
                      END
                      FROM [dbo].[YourTable]


                      Possibly Better Table Solution as this doesn't use as many calls to the functions
                      and reduces the string values in the sub-query one time each:



                      SELECT CASE WHEN CHARINDEX(',', x.[YourColumn]) = 0 THEN x.[YourColumn] ELSE SUBSTRING(x.[YourColumn], 0, CHARINDEX(',', x.[YourColumn], 0)) END AS [YourColumn]
                      FROM
                      (
                      SELECT REPLACE(SUBSTRING([YourColumn], CHARINDEX('txtf_dns_nome=', [YourColumn]), LEN([YourColumn]) - CHARINDEX('txtf_dns_nome', [YourColumn])), 'txtf_dns_nome=', '') AS [YourColumn]
                      FROM [dbo].[YourTable]
                      ) AS x






                      share|improve this answer














                      share|improve this answer



                      share|improve this answer








                      edited Jan 2 at 17:30

























                      answered Jan 2 at 16:17









                      Ryan WilsonRyan Wilson

                      4,0141620




                      4,0141620













                      • After using the tips of colleagues, I was able to do partially:

                        – user3703942
                        Jan 7 at 17:10



















                      • After using the tips of colleagues, I was able to do partially:

                        – user3703942
                        Jan 7 at 17:10

















                      After using the tips of colleagues, I was able to do partially:

                      – user3703942
                      Jan 7 at 17:10





                      After using the tips of colleagues, I was able to do partially:

                      – user3703942
                      Jan 7 at 17:10


















                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009284%2fsql-server-find-value-inside-a-string%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      vnbQRzw,S66r aeO0uqQAN4iy33MWK112dfIBt,GzrKre9,gM1JCkc0owlum,eYwCg8,G6LgktXWuH82Ju8VQcuBg,h
                      4pctbS ooV3zSaU 5ugXirhzc,e2u8UtZ9QK7 C Mbu Q oRrGTd4SmooUGpEDrGVQW58NIpS3rx5 As0

                      Popular posts from this blog

                      Monofisismo

                      Angular Downloading a file using contenturl with Basic Authentication

                      Olmecas