How to Concat String in SQL WHERE clause

Question

I have declared two variables in RAW sql

DECLARE @str nvarchar(max), @str1 nvarchar (max);

SET @str = "  AND (c.BondSales_Confirmed <> -1)";

SET @str1 = "  AND (c.BondSales_IssueType = 'REGULAR')";

My SQL query is:

SELECT * From t_BondSales Where (BondSales_cType <> 'Institute') " + str1 + str  "

Here I get the following error:

Error: SQL Problems: Incorrect Syntax near "+ str1 + str"

Can any one Please help me with the proper syntax about how to concat String in where clause?

It was showing you error just because you left "@" symbol before your variable names in the select query. — Novice, Jul 22 '13 at 07:26

score 20 · Answer 1 · answered Nov 08 '13 at 21:29

20

very easy!! in mysql use CONCAT() function:

SELECT * FROM tbl_person WHERE CONCAT(first_name,' ',last_name) = 'Walter White';

but this does not work in mysql:

SELECT * FROM tbl_person WHERE first_name+' '+last_name = 'Walter White';

answered Nov 08 '13 at 21:29

Amir Hossein Jamsidi

1,980
1
18
10

1

The `CONCAT(first_name, ' ', last_name) = ''` one works with JPA TypedQuery too. Thank you for your help. – alsobubbly Jun 20 '14 at 10:51
2

The question is about MS SQL Server – ruohola Jul 29 '19 at 10:04

score 11 · Accepted Answer · answered Jul 22 '13 at 07:12

11

Try this one -

DECLARE 
       @str NVARCHAR(MAX)
     , @str1 NVARCHAR (MAX);

SELECT 
       @str = ' AND c.BondSales_Confirmed != -1'
     , @str1 = ' AND c.BondSales_IssueType = ''REGULAR''';

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = '
SELECT * 
FROM t_BondSales 
WHERE BondSales_cType != ''Institute'''
     + @str 
     + @str1

PRINT @SQL
EXEC sys.sp_executesql @SQL

answered Jul 22 '13 at 07:12

Devart

119,203
23
166
186

Can you suggest me another last thing, which is: Is this String concatenation is supported in Jasper (iReport)? @Devart – Saquibul Islam Waheed Jul 22 '13 at 10:32
Sorry, but I don't know exactly about that. Possibly - yes. – Devart Jul 22 '13 at 10:36

score 2 · Answer 3 · answered Jul 22 '13 at 07:34

Passing column names along with values is subject to SQL Injection. Make sure to read this post www.sommarskog.se/dynamic_sql.html

So I would suggest you to change the code like this

declare @BondSales_Confirmed int
declare @BondSales_IssueType varchar(100)

SELECT * From t_BondSales Where (BondSales_cType <> 'Institute')
AND (c.BondSales_Confirmed <> @BondSales_Confirmed  or @BondSales_Confirmed is null)
AND (c.BondSales_IssueType = @BondSales_IssueType or @BondSales_IssueType is null)

Just pass null value if you do not want to apply a condition to the columns BondSales_Confirmed and BondSales_IssueType

This doesn't serve my purpose actually. Thanks anyway @Madhivanan — Saquibul Islam Waheed, Jul 23 '13 at 04:10

How to Concat String in SQL WHERE clause

3 Answers3