Parse Multiple Substrings Between 2 Characters

Question

Parse Multiple Substrings Between 2 Characters

bellsy

Newbie

Points: 1
More actions
August 14, 2008 at 7:26 pm

#382250

Hi,
I'm having a problem that I just can't seem to solve. I've been googling around this morning and just can't seem to get the right combination of words to give me an answer/clue to the resolution 🙂 I'll try and keep it short..
Stored in a column will be a formula written by the user, and within this formula will be variables that refer to meters that have values stored against it eg.
Formula = [Meter_1] + [Meter_2] - [Meter_3]
Data:
Meter | Date | Value
-------------------------------
Meter_1 | 01/01/2008 | 10
Meter_2 | 01/01/2008 | 50
Meter_3 | 01/01/2008 | 5
...
So for the supplied date of 01/01/2008, the formula would return 55.
Now I have written the SQL that will replace the meter identifiers with the values supplied from the database
create table #temp
(
row_id int IDENTITY(1,1),
meter_id int,
value decimal(20,6)
)
... (manually inserting data)
set @meter_id_replace = (
select '[' + convert(nvarchar(18), #temp.meter_id) + ']'
from #temp
where #temp.row_id = @row_id
)
set @value_replace = (
select convert(nvarchar(21), #temp.value)
from #temp
where #temp.row_id = @row_id
)
set @new_expression = replace(@new_expression, @meter_id_replace, @value_replace)
But I need to get the list of the required meters in the formula into a temp table to go through and perform the replacements.
At the moment I'm using the square brackets to identify the meters in the formula, but any characters could be used.
Would regex be required, or something else?
If more info is required I'm happy to provide it 🙂
Thanks in advance
bellsy

Viewing 3 posts - 1 through 3 (of 3 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

If I understand what you are saying you want to parse the formula to get the names of the inputs for the formula. You should search this site for "Tally Table" and "Tame Those Strings" as there are articles and forums that will give you an idea on how to do what you want to do.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ken McKelvey SSCoach Points: 18975 More actions · Answer 2

-- *** Test Data ***

DECLARE @t TABLE

(

&nbsp&nbsp&nbsp&nbspMeter varchar(20) NOT NULL

&nbsp&nbsp&nbsp&nbsp,MeterDate datetime NOT NULL

&nbsp&nbsp&nbsp&nbsp,Value int NOT NULL

)

INSERT INTO @t

SELECT 'Meter_1', '20080101', 10 UNION ALL

SELECT 'Meter_2', '20080101', 50 UNION ALL

SELECT 'Meter_3', '20080101', 5

-- *** End Test Data ***

DECLARE @FormulaString varchar(255)

&nbsp&nbsp&nbsp&nbsp,@SQLString nvarchar(255)

&nbsp&nbsp&nbsp&nbsp,@Result int

SET @FormulaString = '[Meter_1] + [Meter_2] - [Meter_3]'

-- do the replace

SELECT @FormulaString

&nbsp&nbsp&nbsp&nbsp= REPLACE(@FormulaString

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,'[' + Meter + ']'

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CAST(Value as varchar(20))

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp)

FROM @t

WHERE MeterDate = '20080101'

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND @FormulaString LIKE '%[' + Meter + ']%'

-- get the result

SET @SQLString = 'SELECT @Value = ' + @FormulaString

EXEC sp_executesql

&nbsp&nbsp&nbsp&nbsp@SQLString

&nbsp&nbsp&nbsp&nbsp,N'@Value int OUTPUT'

&nbsp&nbsp&nbsp&nbsp,@Result OUTPUT

-- show the result

SELECT @Result