Replace null character in a string in sql
Use this:
REPLACE(myString, char(0), '')
Empty Char in Where Clause?
Oracle -- by default -- treats empty strings and NULL
as the same thing.
This can cause awkward behavior, because comparisons to NULL
almost never return true. So a simple expression such as where sou_ordref = ''
never returns true, because it is equivalent to where sou_ordref = NULL
.
Here is one workaround:
SELECT *
FROM SOAUDIT
WHERE (SOU_USER, COALESCE(TRIM(SOU_ORDREF), ' '), SOU_TYPE) IN
( ('proust', ' ', 'S') )
Note that this replaces the empty string (NULL
) with a space. It then compares the results to a space.
SQL delete NULL containing strings
Stackoverflow already has an existing answer:
REPLACE(myString, char(0), '')
Source: Replace null character in a string in sql
This might help too: What is the Null Character literal in TSQL?
SQL statement to check for empty string - T-SQL
I found a blog, https://bbzippo.wordpress.com/2013/09/10/sql-server-collations-and-string-comparison-issues/
which explained that
The problem is because the “default” collation setting
(SQL_Latin1_General_CP1_CI_AS) for SQL Server cannot properly compare
Unicode strings that contain so called Supplementary Characters
(4-byte characters).
A fix is to use a collation that doesn't have problems with the supplementary characters. For example:
select case when N'㴆' COLLATE Latin1_General_100_CI_AS_KS_WS = N'' then 1 else 0 end;
will return 0. See the blog for more examples.
Since you are comparing to the empty string, another solution would be to test the string length.
declare @str1 nvarchar(max) =N'㴆';
select case when len(@str1) = 0 then 1 else 0 end;
This will return 0 as expected.
This also yields 0 when the string is null.
EDIT:
Thanks to devio's comment, I dug a bit deeper and found a comment from Erland Sommarskog https://groups.google.com/forum/#!topic/microsoft.public.sqlserver.server/X8UhQaP9KF0
that in addition to not supporting Supplementary Characters, the Latin1_General_CP1_CI_AS collation doesn't handle new Unicode characters correctly. So I'm guessing that the 㴆 character is a new Unicode character.
Specifying the collation Latin1_General_100_CI_AS
will also fix this issue.
How to create an empty string literal within SQL string
Assuming you are querying SQL Server:
you need to be using the single quote character and escaping the single quotes you want to include in the query string you appear to be building.
declare @ContactNo varchar(10) = '9876543210'
declare @sql varchar(max) = 'select... from... where... and (u.user_contact_no LIKE ''%' + @ContactNo + '%'' or ISNULL(u.user_contact_no,'''') = '''')'
select @sql
returns
select... from... where... and (u.user_contact_no LIKE '%9876543210%' or ISNULL(u.user_contact_no,'') = '')
so to create an empty string literal within your SQL string you need to escape both single quotes so you end up with four single quote characters.
In SQL Server, replace a Char(0), the null character, embedded in a string with its hex code
What you want might be this.
SELECT REPLACE('t'+Char(0)+'t', Char(0), CONVERT(VARCHAR(10),REPLACE(master.dbo.fn_varbintohexstr(0), '000000', '')))
You need to convert it explicitly to VARCHAR
and if you want the full expression remove inner replace
SELECT REPLACE('t'+Char(0)+'t', Char(0), CONVERT(VARCHAR(10),master.dbo.fn_varbintohexstr(0)))
Related Topics
SQL Multiple Columns in In Clause
Hierarchical Data in Linq - Options and Performance
Postgresql Sequence Based on Another Column
Are Stored Procedures More Efficient, in General, Than Inline Statements on Modern Rdbms'S
How to Send Email from Postgresql Trigger
Postgres Window Function and Group by Exception
Are a Case Statement and a Decode Equivalent
Could Not Find Stored Procedure 'Dbo.Aspnet_Checkschemaversion'
Mysql, Iterate Through Column Names
Which SQL Query Is Faster? Filter on Join Criteria or Where Clause
How to Query a Comma Separated Column for a Specific Value
Copy Rows from One Table to Another, Ignoring Duplicates