Nested REPLACE statements Using Multiple Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested REPLACE statements Using Multiple Columns

Author

Topic

KevMull
Starting Member

11 Posts

Posted - 2007-06-20 : 12:51:28

I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.

Nesting them in the usual way doesn't seem to work as that only allows for one column.

My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)

My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.

Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.

FUNCTION GetFormula
(@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200)
AS
BEGIN
DECLARE @strExpression nvarchar(200)

SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',@intTotalNumber)
SELECT @strExpression=REPLACE(@strExpression, 'PF',@numPF )
SELECT @strExpression=REPLACE(@strExpression, 'Hours',@numHours )
RETURN @strExpression
END

Many Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 13:14:39

[code]FUNCTION GetFormula
(@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200)
AS
BEGIN
DECLARE @strExpression nvarchar(200)

SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',convert(varchar, isnull(@intTotalNumber, '')),
@strExpression=REPLACE(@strExpression, 'PF',convert(varchar, isnull(@numPF, '')),
@strExpression=REPLACE(@strExpression, 'Hours',convert(varchar, isnull(@numHours, ''))
RETURN @strExpression
END[/code]

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 13:15:52

I'm surprised this works without you explicitly CONVERTing the Numerics, but maybe SQL Server is doing an implicit Cast.

SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber', COALESCE(CONVERT(varchar(20), @intTotalNumber), ''))
SELECT @strExpression=REPLACE(@strExpression, 'PF',COALESCE(CONVERT(varchar(20), @numPF), '') )
SELECT @strExpression=REPLACE(@strExpression, 'Hours', COALESCE(CONVERT(varchar(20), @numHours), '') )

you can nest these, which may be faster:


SELECT	@strExpression =
	REPLACE(REPLACE(REPLACE(
		@strFormula, 
		'TotalNumber', COALESCE(CONVERT(varchar(20), @intTotalNumber), ''))),
		'PF',COALESCE(CONVERT(varchar(20), @numPF), '')),
		'Hours', COALESCE(CONVERT(varchar(20), @numHours), ''))

Kristen

KevMull
Starting Member

11 Posts

Posted - 2007-06-21 : 06:10:36

Thanks Kristen

The first method works a treat but i'm getting an error if I try rhe nested method ('Replace function requires 3 arguments'). Must be a comma or two out of place or missing somewhere. But I'm more than happy to use the first method so no need to purseue it.

The next stage is to 'evaluate' these string formulae as mathematical formulae and produce a numeric result.

Any ideas?

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources