get numeric values from this string

Question

get numeric values from this string

info 58414

Hall of Fame

Points: 3386
More actions
July 29, 2015 at 2:19 am

#319412

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
🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

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/

x SSC-Insane Points: 23660 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

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/

WayneS SSC Guru Points: 95405 More actions · Answer 4

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

x SSC-Insane Points: 23660 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions