SQL query - filter field containing only spaces
I need to write a sql query that filters out rows that change the number of spaces in a field. For example, I have this request
SELECT MEMO_SYSTEM_TXT
FROM [EE].[dbo].[EE_Billing_Memo]
where MEMO_SYSTEM_TXT is not null and MEMO_SYSTEM_TXT <> '' and MEMO_SYSTEM_TXT <> ' '
I found out that the MEMO_SYSTEM_TXT field can contain a different number of spaces, so my limits are not sufficient. Does anyone have a reliable where is the cluase that will filter out all whitespace at once?
source to share
multiple spaces will always equal an empty string
SELECT 1
WHERE
'a' = 'a ' and
'a' = 'a ' and
'' = ' ' and
cast('' as char(1)) = cast(' ' as char(5))
Returns 1 because they are all equal
So, all you have to do is this:
SELECT MEMO_SYSTEM_TXT
FROM [EE].[dbo].[EE_Billing_Memo]
WHERE MEMO_SYSTEM_TXT is not null and MEMO_SYSTEM_TXT <> ''
source to share
The easiest way is to replace spaces with empty strings and check the length of the string i.e. express your condition like this:
AND LEN(REPLACE(MEMO_SYSTEM_TXT , ' ', '')) = 0
All empty lines and lines consisting of any number of spaces will be found here:
'', ' ', ' ', ' '...
so this can replace the original expression:
and MEMO_SYSTEM_TXT <> '' and MEMO_SYSTEM_TXT <> ' '
and all the others MEMO_SYSTEM_TXT <> ' '
that you have to include.
source to share
You can use a RegEx-like condition, however the supported functionality is very limited in native T-SQL.
SELECT
MEMO_SYSTEM_TXT
FROM
[EE].[dbo].[EE_Billing_Memo]
WHERE
MEMO_SYSTEM_TXT LIKE N'%[^ ]%'
Examples of values
SELECT
*
FROM
(VALUES (''), (' '), (' '), ('x y'), (' x'), ('x ')) AS A(txt)
WHERE
txt LIKE N'%[^ ]%'
If it is not a time query, update the data stored in the column, update all records to remove all space-only values (update them to NULL or empty string).
You can add a CHECK
column constraint to prevent new "empty" records from appearing.
Updating all 'empyt' values to NULL
UPDATE
[EE].[dbo].[EE_Billing_Memo]
SET
MEMO_SYSTEM_TXT = NULL
WHERE
MEMO_SYSTEM_TXT LIKE N'%[^ ]%'
Limitation CHECK
for adding:
CONSTRAINT CK_PreventEmpty_MEMO_SYSTEM_TXT
CHECK MEMO_SYSTEM_TXT LIKE N'%[^ ]%'
An alternative solution is to add a trigger INSTEAD OF
( INSERT
and UPDATE
) to prevent "empty" values MEMO_SYSTEM_TXT
, or you can create an indexed view, which does not contain "empty" text.
source to share