July 29, 2015 at 2:19 am
Hi,
why can i get the numeric values from this stiring?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be diffrent...
Thanks and Regards
Nicole
🙂
July 29, 2015 at 7:44 am
info 58414 (7/29/2015)
Hi,why can i get the numeric values from this stiring?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be diffrent...
Thanks and Regards
Nicole
🙂
Not much detail here to work with. Is this data in a table? Is this format consistent? You might be able to use a combination of CHARINDEX and SUBSTRING. You might need something a bit more powerful like DelimitedSplit8K.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2015 at 7:57 am
Sean Lange (7/29/2015)
info 58414 (7/29/2015)
Hi,why can i get the numeric values from this stiring?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be diffrent...
Thanks and Regards
Nicole
🙂
Not much detail here to work with. Is this data in a table? Is this format consistent? You might be able to use a combination of CHARINDEX and SUBSTRING. You might need something a bit more powerful like DelimitedSplit8K.
I agree! Its an interesting topic, plus, do you really want to do this in T-SQL?
I'm pretty sure my attempt won't fit the ACTUAL requirements, but technically it does split the values 🙂
WITH LISTSRC AS (
SELECT ';' + '{_cpn}=1743; {_cpnll}=4511' + ';' AS LISTFIELD
)
,
TALLYTAB AS (
SELECT B.VAL1 * 10 + A.VAL1 TALLYVAL FROM
(
SELECT 0 VAL1 UNION SELECT 1 VAL1 UNION SELECT 2 VAL1 UNION SELECT 3 VAL1 UNION SELECT 4 VAL1 UNION
SELECT 5 VAL1 UNION SELECT 6 VAL1 UNION SELECT 7 VAL1 UNION SELECT 8 VAL1 UNION SELECT 9 VAL1
) A
CROSS JOIN
(
SELECT 0 VAL1 UNION SELECT 1 VAL1 UNION SELECT 2 VAL1 UNION SELECT 3 VAL1 UNION SELECT 4 VAL1 UNION
SELECT 5 VAL1 UNION SELECT 6 VAL1 UNION SELECT 7 VAL1 UNION SELECT 8 VAL1 UNION SELECT 9 VAL1
) B
)
,
SPLIT_SEMI AS (
SELECT TALLYVAL TV1, '=' + SUBSTRING(LISTFIELD, TALLYVAL + 1, CHARINDEX(';',LISTFIELD,TALLYVAL + 1) - TALLYVAL - 1) + '=' AS VAL_SEMI
FROM TALLYTAB CROSS JOIN LISTSRC
WHERE TALLYVAL < LEN(LISTFIELD) AND SUBSTRING(LISTFIELD,TALLYVAL,1) = ';'
)
,
SPLIT_EQ AS (
SELECT TALLYVAL, CHARINDEX('=',VAL_SEMI, TALLYVAL + 1) TALLY1_END, VAL_SEMI, TV1,
SUBSTRING(VAL_SEMI, TALLYVAL + 1, CHARINDEX('=',VAL_SEMI,TALLYVAL + 1) - TALLYVAL - 1) VAL_EQ
FROM TALLYTAB CROSS JOIN SPLIT_SEMI
WHERE TALLYVAL < LEN(VAL_SEMI) AND SUBSTRING(VAL_SEMI,TALLYVAL,1) = '='
)
,
SPLIT_POS AS (
SELECT ROW_NUMBER () OVER (PARTITION BY 1 ORDER BY TALLYVAL, TV1) RN, TALLYVAL, TV1, LTRIM(RTRIM(VAL_EQ)) VAL_PARSED FROM SPLIT_EQ
)
SELECT (SELECT VAL_PARSED FROM SPLIT_POS WHERE RN = 1) VAL1_LABEL, (SELECT VAL_PARSED FROM SPLIT_POS WHERE RN = 3) VAL1_TEXT,
(SELECT VAL_PARSED FROM SPLIT_POS WHERE RN = 2) VAL2_LABEL, (SELECT VAL_PARSED FROM SPLIT_POS WHERE RN = 4) VAL2_TEXT
July 29, 2015 at 8:39 am
Here is another alternative.
with Something(someVal) as
(
select '{_cpn}=1743; {_cpnll}=4511'
)
select xx.Item
from Something s
cross apply dbo.DelimitedSplit8K(s.someVal, ';') x
cross apply dbo.DelimitedSplit8K(x.Item, '=') xx
where xx.ItemNumber = 2
You can find the DelimitedSplit8K by following the article in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 29, 2015 at 8:40 am
info 58414 (7/29/2015)
Hi,why can i get the numeric values from this stiring?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be diffrent...
Thanks and Regards
Nicole
🙂
This code is based upon there being only two numbers that you are looking for in the string. Remarks are in the code.
DECLARE @test-2 VARCHAR(100) = '{_cpn}=1743; {_cpnll}=4511';
WITH cte AS
(
SELECT ca8.Position, ca8.Value
-- use PATINDEX to find the first numeric character in the string
FROM (SELECT PATINDEX('%[0-9]%', @test-2) ) dt(Pos)
-- get the string from this position to the end of the string. The string starts with the numbers.
CROSS APPLY (SELECT SUBSTRING(@Test, dt.Pos, LEN(@Test))) ca1(NewString)
-- use PATINDEX to find the first non-numeric character in the string
CROSS APPLY (SELECT PATINDEX('%[^0-9]%', ca1.NewString)) ca2(Pos)
-- get the string from this first non-numeric character to the end of the string.
CROSS APPLY (SELECT SUBSTRING(ca1.NewString, ca2.Pos, LEN(ca1.NewString))) ca3(NewString)
-- use PATINDEX to find the first numeric character in the string
CROSS APPLY (SELECT PATINDEX('%[0-9]%', ca3.NewString)) ca4(Pos)
-- get the string from this numeric character to the end of the string
CROSS APPLY (SELECT SUBSTRING(ca3.NewString, ca4.Pos, LEN(ca3.NewString))) ca5(NewString)
-- use PATINDEX to find the first non-numeric character in the string
CROSS APPLY (SELECT PATINDEX('%[^0-9]%', ca5.NewString)) ca6(Pos)
-- this string starts with the numbers, get the left characters up to but not including the first non-numeric character
CROSS APPLY (SELECT LEFT(ca1.NewString, ca2.Pos-1),
-- if there isn't a non-numeric character, return the entire string.
-- if there is, return the left characters up to but not including the first non-numeric character
CASE WHEN ca6.Pos = 0 THEN ca5.NewString ELSE LEFT(ca5.NewString, ca6.pos-1) END) ca7(Num1, Num2)
CROSS APPLY (SELECT * FROM (VALUES (1, ca7.Num1), (2, ca7.Num2)) dt(Position, Value)) ca8
)
SELECT STUFF((SELECT ', ' + Value
FROM cte
ORDER BY Position
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '');
You can see how this works to create a delimited string at this article[/url].
My results:
----------
1743, 4511
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 29, 2015 at 9:43 am
Yep, go with Seans approach, much better than mine!
The only thing I would add would be to try to maintain the associations between the label and the value.
edit: Just wanted to add that Seans fits the bill based on the original request, I was just trying to expand on this as I thought it would be cool to associate the labels with the values.
with Something(rowid, someVal) as
(
select 'row1' rowid, '{_label1}=1743; {_label2}=4511;{_label3}=6359' someVal
union
select 'row2' rowid, '{_label3}=1741; {_label1}=4514;{_label2}=6361' someVal
union
select 'row3' rowid, '{_label5}=1755; {_label4}=4518;{_label3}=6362;{labelwhatever}=9988' someVal
)
,
items_semi(rowid, Item_semi, Itemnumber_semi) as
(
select rowid, Item, ItemNumber
from Something s
cross apply dbo.DelimitedSplit8K(s.someVal, ';') x
)
,
items_eq(rowid, Item_eq, Itemnumber_eq, itemnumber_semi) as
(
select rowid, Item, Itemnumber, itemnumber_semi from
items_semi
cross apply dbo.DelimitedSplit8K(Item_semi, '=') xx
)
,
items_label(rowid, item_label, item_value) as (
select item_labels.rowid_label, item_label, item_value from
(
select rowid as rowid_label, item_eq as item_label, itemnumber_semi as item_label_id from
items_eq where itemnumber_eq = 1
) item_labels
join
(
select rowid as rowid_value, item_eq as item_value, itemnumber_semi as item_value_id from
items_eq where itemnumber_eq = 2
) item_values
on
rowid_label = rowid_value and item_label_id = item_value_id
)
select rowid, rtrim(ltrim(item_label)) item_label, rtrim(ltrim(item_value)) item_value from items_label
July 29, 2015 at 10:06 pm
info 58414 (7/29/2015)
Hi,why can i get the numeric values from this stiring?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be diffrent...
Thanks and Regards
Nicole
🙂
Will there only ever be and always be two numeric values in the string?
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply