how extract values from expressions

Question

Post reply

how extract values from expressions

chalam87

Right there with Babe

Points: 771
More actions
December 10, 2015 at 6:02 am

#313890

Hi,
how extract values from expressions
declare @testdata table(expression varchar(500))
insert into @testdata values('[w1+w2+w3/5]'),('[(w1+w2+w3)/5]'),('[(w4-w5*w6)/5]')
select * from @testdata
Result like
w1
w2
w3
w1
w2
w3
w4
w5
w6
--chalam

Viewing 5 posts - 1 through 4 (of 4 total)

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

ben.brugman SSChampion Points: 13369 More actions · Answer 1

Search on the forum for:

DelimitedSplit8K

Recipe:

Replace all chars/strings which are not a value with an 'X', or another character which is not used.

Use the DelimitedSplit8K function to split the string into the seperate parts.

Do any further filtering.

I use a self build Replace2 function instead of a number of replaces.

Here the number 5 is not filtered out, because I do not know what is to be considered a value and what is not. So this is not complete but can be used as a start.

Tell us if this does help.

Ben

Edit.

Add the new characters and the replacement for this to the set of chars to replace.

('[(w4-w5*w6)/5]') so ad a replace for the '*' as wel.

update @testdata set expression = master.dbo.Replace2(expression,'[|+|/|*|-|]|(|)|XXXX|XX|XX|','X|X|X|X|X|X|X|X|X|X|X|X')

declare @testdata table(expression varchar(500))

insert into @testdata values('[w1+w2+w3/5]'),('[(w1+w2+w3)/5]')

update @testdata set expression = master.dbo.Replace2(expression,'[|+|/|]|(|)|XXXX|XX|XX|','X|X|X|X|X|X|X|X|X|X')

-- Or use the replace function to do this for each character/string independend.

select * from @testdata

SELECT item,* FROM @testdata

CROSS APPLY DelimitedSplit8K(expression, 'x') a

WHERE Item <> ''

-- OR

SELECT distinct item FROM @testdata

CROSS APPLY DelimitedSplit8K(expression, 'x') a

WHERE Item <> ''

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

The DelimitedSplit8K is a great tool, but you might find useful to use another tool to reduce the code.

This tool is the Pattern Splitter created by Chris Morris and posted by Dwain Camps in here: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

Then, the code might look like this:

declare @testdata table(expression varchar(500))

insert into @testdata values('[w1+w2+w3/5]'),('[(w1+w2+w3)/5]'),('[(w4-w5*w6)/5]')

SELECT expression, Item

FROM @testdata

--Uses REPLACE because the splitter doesn't seem to be escaping the right square bracket correctly

CROSS APPLY dbo.PatternSplitCM(REPLACE( expression, ']', '['), '%[-()[+=*/]%')

WHERE Matched = 0 --Discard operators

AND Item LIKE '%[^0-9]%' --Discard literals

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Another way, using delimitedsplit8K and patreplace8k (both referenced in my signature) would be like so:

declare @testdata table(expression varchar(500))

insert into @testdata

SELECT expression FROM

(values('[w1+w2+w3/5]'),('[(w1+w2+w3)/5]'),('[(w4-w5*w6)/5]')) x(expression)

select expression, Item

from @testdata

CROSS APPLY dbo.Patreplace8K(expression,'[^w0-9]','|')

CROSS APPLY dbo.delimitedsplit8k(newstring,'|')

WHERE Item LIKE 'w[0-9]%';

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ScottPletcher SSC Guru Points: 100962 More actions · Answer 4

If it's always a "w" followed by a digit or digits, perhaps just this?!:

declare @testdata table(expression varchar(500))

insert into @testdata values('[w1+w2+w3/5]'),('[(w1+w2+w3)/5]'),('[(w4-w5*w6)/5]')

insert into @testdata values('w1')

insert into @testdata values('2w3')

insert into @testdata values('4+w5')

select 'w' + LEFT(ds.item, patindex('%[^0-9]%', ds.Item + '.') - 1)

from @testdata td

cross apply dbo.DelimitedSplit8K(td.expression, 'w') ds

where ds.ItemNumber > 1 AND ds.Item like '[0-9]%'

Edit: Corrected code and added more test data.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.