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?
SELECT
MEMO_SYSTEM_TXT
FROM [EE].[dbo].[EE_Billing_Memo]
WHERE
MEMO_SYSTEM_TXT IS NOT NULL
AND LTRIM(MEMO_SYSTEM_TXT) <> ''
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 <> ''
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.
Opens MEMO_SYSTEM_TXT
that are not empty or empty string and do not contain spaces.
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 NOT LIKE '% %'
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.
You can use regular expression expressions in SQL. Find the regex expression that matches what you want to detect and apply it directly in the WHERE clause.