August 18, 2015 at 11:20 am
Hello
Can you please let me know how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
INSERT INTO @T1 VALUES
('FN1', 'LN1', 'A11', 'A12'),
('FN2', 'LN2', 'A21', 'A22'),
('FN3', 'LN2', 'A31', 'A32')
SELECT * FROM @T1
/*
-- OUTPUT
FirstName: FN1,LastName: LN1,Add1:A11,Add2:A12
FirstName: FN2,LastName: LN2,Add1:A21,Add2:A22
FirstName: FN3,LastName: LN2,Add1:A31,Add2:A32
*/
Thanks
Shuaib
August 18, 2015 at 11:43 am
ShuaibV (8/18/2015)
HelloCan you please let me know how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
INSERT INTO @T1 VALUES
('FN1', 'LN1', 'A11', 'A12'),
('FN2', 'LN2', 'A21', 'A22'),
('FN3', 'LN2', 'A31', 'A32')
SELECT * FROM @T1
/*
-- OUTPUT
FirstName: FN1,LastName: LN1,Add1:A11,Add2:A12
FirstName: FN2,LastName: LN2,Add1:A21,Add2:A22
FirstName: FN3,LastName: LN2,Add1:A31,Add2:A32
*/
Thanks
Shuaib
Quick suggestion
😎
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
INSERT INTO @T1 VALUES
('FN1', 'LN1', 'A11', 'A12'),
('FN2', 'LN2', 'A21', 'A22'),
('FN3', 'LN2', 'A31', 'A32')
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(@V1,'@FN',TX.FN),'@LN',TX.LN),'@A1',TX.A1),'@A2',TX.A2) AS OUTSTR
FROM @T1 TX;
Results
OUTSTR
----------------------------------------------------
FirstName: FN1, LastName: LN1, Add1: A11, Add2: A12
FirstName: FN2, LastName: LN2, Add1: A21, Add2: A22
FirstName: FN3, LastName: LN2, Add1: A31, Add2: A32
August 18, 2015 at 11:57 am
Thank you for your quick response! The variable counts are not defined. Is it possible to create any function and use it?
Example:
SELECT ('%1 %2 %3', 1, 2, 3);
SELECT ('%1 %2', 1,2);
Output:
1,2,3
1,2
Thanks
August 18, 2015 at 12:41 pm
Are you looking for something like this?
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE name = 'Replacer'
AND type in (N'FN', N'IF', N'TF')
AND objects.schema_id = SCHEMA_ID('dbo')
)
DROP FUNCTION dbo.Replacer
GO
CREATE FUNCTION Replacer
(
@Template varchar(max),
@Find1 varchar(10),
@Replace1 varchar(100),
@Find2 varchar(10) = null,
@Replace2 varchar(100) = null,
@Find3 varchar(10) = null,
@Replace3 varchar(100) = null
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @s-2 varchar(MAX);
SET @s-2 = @Template;
IF @Find1 is NOT NULL and @Replace1 is NOT NULL SET @s-2 = REPLACE(@s, @Find1, @Replace1);
IF @Find2 is NOT NULL and @Replace2 is NOT NULL SET @s-2 = REPLACE(@s, @Find2, @Replace2);
IF @Find3 is NOT NULL and @Replace3 is NOT NULL SET @s-2 = REPLACE(@s, @Find3, @Replace3);
RETURN @s-2;
END
GO
SELECT dbo.Replacer('FirstName: @FN', '@FN', 'Bill', DEFAULT, DEFAULT, DEFAULT, DEFAULT);
SELECT dbo.Replacer('FirstName: @FN, LastName: @LN', '@FN', 'Bill', '@LN', 'Moyer', DEFAULT, DEFAULT);
August 18, 2015 at 12:51 pm
SQL Server doesn't allow user defined functions with variable parameters. All the parameters must be included every single time. There could be an option if you would take 2 strings, one for the original string and one with comma-delimited values (or any other character). Would that help you?
August 18, 2015 at 12:55 pm
My replacer function above is fairly generic and you could probably make it more so. Without going to dynamic sql you're pretty limited. Sql Server doesn't have a printf function or allow you to not send defaulted parameters positioned at the end like most languages allow. There is also no polymorphism so you can't change the datatypes from varchars without changing the function name.
SELECT dbo.Replacer('Name: @FN, Created: @cr', '@FN', name, '@cr', CAST(createdate AS varchar(20)), DEFAULT, DEFAULT)
from sys.sysusers s
;
August 18, 2015 at 1:54 pm
Thank you! I was looking for something like this.
Thanks
August 19, 2015 at 7:06 am
This was the option that I mentioned yesterday, but I had to get out of the office and couldn't post it.
It will replace any number of instances and has the option to show or remove unused variables, as well as defining how to identify variables so you can use the character you want.
It's not perfect, but it can be useful.
IF OBJECT_ID( 'dbo.iReplacer') IS NOT NULL
DROP FUNCTION dbo.iReplacer
GO
CREATE FUNCTION dbo.iReplacer
(
@String varchar(8000),
@Replacement varchar(8000),
@VariableIdentifier char(1),
@ShowUnusedVariables bit
)
RETURNS TABLE
AS
RETURN
SELECT (SELECT CASE WHEN o.ItemNumber = 1 THEN o.Item
WHEN r.Item IS NULL AND @ShowUnusedVariables = 1 THEN @VariableIdentifier + o.Item
ELSE STUFF( o.Item, 1, PATINDEX( '%[^A-Za-z0-9]%', o.Item + ' ') - 1, r.Item) END
FROM dbo.DelimitedSplit8K( @String, @VariableIdentifier) o
LEFT
JOIN dbo.DelimitedSplit8K( @Replacement, ',') r ON o.ItemNumber = r.ItemNumber + 1
ORDER BY o.ItemNumber
FOR XML PATH(''),TYPE).value('.', 'varchar(8000)') AS NewString
GO
CREATE TABLE #SampleData(
String varchar(8000),
Replacements varchar(8000),
VariableIdentifier char(1),
ShowUnusedVariables bit)
INSERT INTO #SampleData
VALUES
('%1 %2 %3', '1,2,3', '%', 0),
('%1 %2 %3', '1,2', '%', 0),
('%1 %2 %3', '1,2', '%', 1),
('%1 %2', '1,2', '%', 0),
('FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 ', 'FN1,LN1,A11,A12', '@', 0),
('FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 ', 'FN2,LN2,A21,A22', '@', 0),
('FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 ', 'FN3,LN2,A31', '@', 0),
('FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 ', 'FN3,LN2,A31', '@', 1),
('FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 ', 'FN3,LN2,A31', '@', 1);
SELECT *
FROM #SampleData
CROSS APPLY dbo.iReplacer(String, Replacements, VariableIdentifier, ShowUnusedVariables);
GO
DROP TABLE #SampleData
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply