Oracle to SQL Server conversion

Question

What is the equivalent of SQL Server query form for the below Oracle query in a LIKE statement:

((UPPER(ADDRESS) like '%'|| UPPER(:VALUE1) || '%' ) OR (ADDRESS IS NULL AND :VALUE1 IS NULL))

I am stuck with the syntax '%'||------||'%'.

Pat · Answer 1 · 2014-05-22T18:09:18.503

0

You can use + instead of || to concatenate the strings

((UPPER(ADDRESS) like '%'+ UPPER(VALUE1) + '%' ) OR (ADDRESS IS NULL AND VALUE1 IS NULL))

You also do not need to use upper in comparison for SQL Server. Comparisons in SQL Server are case insensitive.

Is this part of a where clause or something?

edited May 22 '14 at 18:09

answered May 22 '14 at 17:57

Pat

Yes Pat , It is in Where clause only ..Thank you – user3640834 May 22 '14 at 18:02
by default SQL Server is case insensitive, but there are case sensitive collations also. e.g. see: http://stackoverflow.com/questions/1411161/sql-server-check-case-sensitivity – Paul Maxwell Feb 19 '15 at 11:12

score 0 · Answer 2 · answered May 22 '14 at 17:57

|| is Oracle's concatenation operator. SQL Server's equivilent is +.

((UPPER(ADDRESS) like '%' + UPPER(@VALUE1) + '%' ) 
OR (ADDRESS IS NULL AND @VALUE1 IS NULL))

Incidentally, || is the SQL standard, + is used by some databases, like SQL Server instead of following the standard.

score 0 · Answer 3 · answered May 22 '14 at 17:58

0

((UPPER(ADDRESS) like '%' + UPPER(VALUE1) + '%' ) OR (ADDRESS IS NULL AND VALUE1 IS NULL))

Note that using upper is only necessary depending on the collation setting.

answered May 22 '14 at 17:58

Dave.Gugg

3 Answers3