CODEWITHSUNDEEP
sqlsql-serversql-server-2012whitespaceremoving-whitespace
user3896424
user3896424

Reputation: 37

Query for blank white space before AND after a number string

enter image description hereHow would i go about constructing a query, that would return all material numbers that have a "blank white space" either BEFORE or AFTER the number string? We are exporting straight from SSMS to excel and we see the problem in the spreadsheet. If i could return all of the material numbers with spaces.. i could go in and edit them or do a replace to fix this issue prior to exporting! (the mtrl numbers are imported in via a windows application that users upload an excel template to. This template has all of this data and sometimes they place in spaces in or after the material number). The query we have used to work but now it does not return anything, but upon export we identify these problems you see highlighted in the screenshot (left screenshot) and then query to find that mtrl # in the table (right screenshot). And indeed, it has a space before the 1.

enter image description here

Currently the query we use looks like:

SELECT  Mtrl
FROM dbo.Source
WHERE Mtrl LIKE '% %'

Upvotes: 1

Views: 3941

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

Since you are getting the data from a query, you should just have that query remove any potential spaces using LTRIM and RTRIM:

LTRIM(RTRIM([MTRL]))

Keep in mind that these two commands remove only spaces, not tabs or returns or other white-space characters.

Doing the above will make sure that the data for the entire set of data is fine, whether or not you find it and/or fix it.

Or, since you are copying-and-pasting from the Results Grid into Excel, you can just CONVERT the value to a number which will naturally remove any spaces:

SELECT CONVERT(INT, ' 12 ');

Returns:

12

So you would just use:

CONVERT(INT, [MRTL])

Now, if you want to find the data that has anything that is not a digit in it, you would use this:

SELECT Mtrl
FROM   dbo.Source
WHERE  [Mtrl] LIKE '%[^0-9]%'; -- any single non-digit character

If the issue is with non-space white-space characters, you can find out which ones they are via the following (to find them at the beginning instead of at the end, change the RIGHT to be LEFT):

;WITH cte AS
(
  SELECT UNICODE(RIGHT([MTRL], 1)) AS [CharVal]
  FROM dbo.Source
)
SELECT *
FROM cte
WHERE cte.[CharVal] NOT BETWEEN 48 AND 57 -- digits 0 - 9
AND cte.[CharVal] <> 32; -- space

And you can fix in one shot using the following, which removes regular spaces (char 32 via LTRIM/RTRIM), tabs (char 9), and non-breaking spaces (char 160):

UPDATE src
SET    src.[Mtrl] = REPLACE(
                            REPLACE(
                                    LTRIM(RTRIM(src.[Mtrl])),
                                    CHAR(160),
                                    ''),
                            CHAR(9),
                            '')
FROM   dbo.Source src
WHERE  src.[Mtrl] LIKE '%['    -- find rows with any of the following characters
                          + CHAR(9) -- tab
                          + CHAR(32) -- space
                          + CHAR(160) -- non-breaking space
                          + ']%';

Here I used the same WHERE condition that you have since if there can't be any spaces then it doesn't matter if you check both ends or for any at all (and maybe it is faster to have a single LIKE instead of two).

Upvotes: 1

Related Questions