Why is REPLACE not working in my SQL query?

Question

I am creating a stored procedure. Currently queries are being stored in variables. For example:

@sample = 'SELECT * FROM WS_CoolApp_ASample'
@sample += 'Where DATASOURCE.FName = @finder AND DATASOURCE.Age = 23'

I want to remove that 'DATASOURCE.' from the query if a condition is true. So far I've tried:

IF (true)
    REPLACE(@sample, "DATASOURCE.", "")

Why isn't this working?

Which database engine is this, is it SQL Server? Please be explicit. — DavidG, Sep 06 '16 at 00:28
Also, your SQL string needs an additional space character after the table name or it runs into the where clause. — DavidG, Sep 06 '16 at 00:29

Mike D. · Answer 1 · 2016-09-06T01:34:36.627

3

You aren't assigning the output of REPLACE() to anything.

@sample = REPLACE(@sample, 'DATASOURCE.', '')

edited Sep 06 '16 at 01:34

answered Sep 06 '16 at 00:26

Mike D.

4,034
2
26
41

Ok, actually you were right. I had an if statement with an else statement below it and I changed it to this in the if statement but forgot to do it in the else statement. It's working now! – AleAng Sep 06 '16 at 14:36

score 0 · Answer 2 · answered Sep 06 '16 at 05:15

<!-- language: lang-sql -->
DECLARE @sample as nvarchar(500)

set @sample = 'SELECT * FROM WS_CoolApp_ASample';
set @sample += 'Where DATASOURCE.FName = @finder AND DATASOURCE.Age = 23';

-- assign the replaced string back to @sample
set @sample = REPLACE(@sample, 'DATASOURCE.', '')

PRINT @sample

-- prints -> SELECT * FROM WS_CoolApp_ASampleWhere FName = @finder AND Age = 23

score 0 · Answer 3 · answered Sep 06 '16 at 14:01

0

DECLARE @sample nvarchar(max)
SET @sample = 'SELECT * FROM WS_CoolApp_ASample'
SET @sample += 'Where DATASOURCE.FName = @finder AND DATASOURCE.Age = 23'

SELECT REPLACE(@sample,'DATASOURCE.','')

I hope this works.

answered Sep 06 '16 at 14:01

Vikram

197
2
6

You missed the space between the table and WHERE Also what about the conditional check? – Marinus Sep 23 '16 at 13:38

score 0 · Answer 4 · answered Sep 23 '16 at 13:37

0

DECLARE @sample nvarchar(max)
SET @sample = 'SELECT * FROM WS_CoolApp_ASample '
SET @sample += 'Where DATASOURCE.FName = @finder AND DATASOURCE.Age = 23'
if(@bit_var = 1)
  SET @sample = REPLACE(@sample,'DATASOURCE.','')

answered Sep 23 '16 at 13:37

Marinus

157
6

Why is REPLACE not working in my SQL query?

4 Answers4