SQL query - filter out field containing only spaces

Question

I need to write a sql query that filters out rows that have a changing number of spaces in a field. For example, I have this query

   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 field MEMO_SYSTEM_TXT might contain different number of spaces, so my restrictions are not sufficient. Anyone have a robust where cluase that will filter out all spaces at once ?

Do you mean filtering out any string that contains spaces apart from other characters (like `'AB CD EF'`), or strings that are composed only of characters (like `' '` or `' '`)? — JotaBe, Sep 16 '14 at 08:32
Could you rephrase you question to prevent getting close for "unclear what you are asking"? E.G. "SQL query - filter out field containing only spaces" — bummi, Sep 16 '14 at 08:48
Yes, sorry. I meant to filter out field's values that contain only spaces, and filter out nulls and empty strings. Is this clear now? — user4045430, Sep 16 '14 at 08:53

Max · Answer 1 · 2014-09-16T09:10:52.050

5

SELECT 
      MEMO_SYSTEM_TXT
FROM [EE].[dbo].[EE_Billing_Memo]
WHERE 
        MEMO_SYSTEM_TXT IS NOT NULL 
    AND LTRIM(MEMO_SYSTEM_TXT) <> ''

edited Sep 16 '14 at 09:10

answered Sep 16 '14 at 08:17

Max

6,821
3
43
59

3

@Max, if the string only contains spaces, you can use only one of LTRIM or RTRIM. You don't need to use both of them. You can improve your answer. – JotaBe Sep 16 '14 at 08:47

score 3 · Answer 2 · answered Sep 16 '14 at 09:19

3

several spaces will always equal empty string

SELECT 1
WHERE 
  'a' = 'a ' and
  'a' = 'a  ' and 
  '' = '   ' and
  cast('' as char(1)) = cast('   ' as char(5))

Returns 1 since 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 <> ''

answered Sep 16 '14 at 09:19

t-clausen.dk

43,517
12
59
92

1

This is understandable in a `char(N)` column, but makes no sense in a `varchar(N)` column. However, it's the way it works. Is this ANSI-SQL or a particular T-SQL behavior? I answer myself: http://support.microsoft.com/kb/316626 – JotaBe Sep 16 '14 at 14:46
@JotaBe if you want to compare with spaces then use: 'a' LIKE 'a ' – t-clausen.dk Sep 16 '14 at 15:29

score 2 · Answer 3 · edited Jan 22 '18 at 10:19

The easiest way is to replace spaces by empty strings, and check the string length, i.e. express your condition like this:

AND LEN(REPLACE(MEMO_SYSTEM_TXT , ' ', '')) = 0

This will find all the empty strings and strings composed of any number of spaces:

'', ' ', '  ', '   '...

so this can replace your original expression:

 and MEMO_SYSTEM_TXT <> '' and MEMO_SYSTEM_TXT <> ' '

and all the rest of MEMO_SYSTEM_TXT <> ' ' that you'd have to include.

score 1 · Answer 4 · answered Sep 16 '14 at 08:15

1

This will filter out MEMO_SYSTEM_TXT which are not null or empty string and do not contain any space.

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 '% %'

answered Sep 16 '14 at 08:15

Felix Pamittan

31,544
7
41
67

-1 This doesn't warranty that you only remove strings composed of only spaces. In fact, you'd remove strings like 'AB CD'. – JotaBe Sep 16 '14 at 08:22
This filters out any string that contain spaces. Isn't this the requirement? – Felix Pamittan Sep 16 '14 at 08:24
I think it isn't, in the Q: "filters out rows that have a changing number of spaces in a field" and `and MEMO_SYSTEM_TXT <> '' and MEMO_SYSTEM_TXT <> ' '` That makes me think that the OP means a field that only has spaces, not that contains spaces apart from other characters. – JotaBe Sep 16 '14 at 08:27
Or the the OP's where clause is wrong and what he actually want is to "filters out rows that have a changing number of spaces in a field" not filter out row that only have a changing number of spaces. – Felix Pamittan Sep 16 '14 at 08:29
He has confirmed in a comment that I'm right, so your answer is definitely not valid. – JotaBe Sep 16 '14 at 08:45

Pred · Answer 5 · 2014-09-16T08:48:45.830

You can use 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'%[^ ]%'

Example values

SELECT
    *
FROM
    (VALUES (''), ('  '), (' '), ('x y'), (' x'), ('x ')) AS A(txt)
WHERE
    txt LIKE N'%[^ ]%'

If this is not a on-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 constraint for the column to prevent newly created 'empty' records.

Updating all 'empyt' values to NULL

UPDATE
  [EE].[dbo].[EE_Billing_Memo]
SET
  MEMO_SYSTEM_TXT = NULL
WHERE
  MEMO_SYSTEM_TXT LIKE N'%[^ ]%'

The CHECK constraint to add:

CONSTRAINT CK_PreventEmpty_MEMO_SYSTEM_TXT
  CHECK MEMO_SYSTEM_TXT LIKE N'%[^ ]%'

An alternative solution is to add an INSTEAD OF trigger (INSERT and UPDATE) to prevent 'empty' values in MEMO_SYSTEM_TXT, OR you can create an indexed view which is not contains the 'empty' texts.

score 0 · Answer 6 · answered Dec 29 '18 at 07:50

0

Try the code below:

SELECT MEMO_SYSTEM_TXT
FROM [EE].[dbo].[EE_Billing_Memo]
WHERE MEMO_SYSTEM_TXT IS NOT NULL AND ASCII(REPLACE(MEMO_SYSTEM_TXT, ' ', '')) != 10

after replacing the spaces with '',only one '' remains and the ascii code of '' is 10

answered Dec 29 '18 at 07:50

Nima Derakhshanjan

1,380
9
24
37

score -1 · Answer 7 · answered Sep 16 '14 at 08:21

-1

You can use regex expressions in SQL. Find the regex expression that matches what you want to detect and apply it directly in the WHERE clause.

answered Sep 16 '14 at 08:21

Pedro Parada

21
2

-1: there is no regex support in SQL Server (at least until current SQL Server 2014) – JotaBe Sep 16 '14 at 08:23
@Pred LIKE and regex have nothing to do. Please, see what regex is: http://en.wikipedia.org/wiki/Regular_expression . You can implement REGEX support in SQL server using SQL CLR integration (i.e http://msdn.microsoft.com/en-us/magazine/cc163473.aspx), but it'snot necessary at all for this case. – JotaBe Sep 16 '14 at 08:44
@JotaBe I know that T-SQL does not cover the entrie Regular Expression functionality, only whildcards. BUT, how do you explain the pattern based wildcards in LIKE clause? – Pred Sep 16 '14 at 08:51
@Pred: No, Pred, T-SQL doesn't support regular expressions at all. There a support for regex in JavaScrip, .NET (C#, VB.NET), perl, java, etc. They have slight differences in the support. But don't say that SQL Server supports regular expressions, because it only have basic wildcards. Noone consider basic wildcards as regular expressions. Besides, you have no way to express any number of spaces using the `LIKE` wildcards. If you use a regex is as simple as `^\s*$`. Does this have to do with like syntax? BTW I'm millionaire, but not fully millionaire: I only have two bucks. – JotaBe Sep 16 '14 at 08:55

SQL query - filter out field containing only spaces

7 Answers7

Linked