SQL Server : find value inside a string

Multi tool use
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.

|
show 1 more comment
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.

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 istxtf_dns_nome=
nottxtf_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
|
show 1 more comment
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.

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.


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 istxtf_dns_nome=
nottxtf_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
|
show 1 more comment
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 istxtf_dns_nome=
nottxtf_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
|
show 1 more comment
3 Answers
3
active
oldest
votes
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.
add a comment |
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 + '%'
add a comment |
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
After using the tips of colleagues, I was able to do partially:
– user3703942
Jan 7 at 17:10
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
add a comment |
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.
add a comment |
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.
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.
answered Jan 2 at 16:02


scsimonscsimon
22.2k51637
22.2k51637
add a comment |
add a comment |
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 + '%'
add a comment |
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 + '%'
add a comment |
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 + '%'
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 + '%'
answered Jan 2 at 16:05


ZamZam
2,41911129
2,41911129
add a comment |
add a comment |
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
After using the tips of colleagues, I was able to do partially:
– user3703942
Jan 7 at 17:10
add a comment |
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
After using the tips of colleagues, I was able to do partially:
– user3703942
Jan 7 at 17:10
add a comment |
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
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
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
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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=
nottxtf_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