-2

SQL :

How to fetch the column values which has spaces in it? Such as space between two words (or) Space in the Prefix or Suffix?

We have over 5000 records. Some of these records has empty space between two words (or) in its trail.

We need to identify those existing records before we apply TRIM or REPLACE syntax for any future insertion.

enter image description here

goofyui
  • 3,362
  • 20
  • 72
  • 128
  • 2
    update your question add a clear data sample for prefix, suffix , two word , space position and the expected result – ScaisEdge Jan 25 '19 at 16:48
  • It sounds like you are having issues with _whitespace_ which may include blanks, tabs, ... . [This](https://stackoverflow.com/questions/35245812/whats-a-good-way-to-trim-all-whitespace-characters-from-a-string-in-t-sql-witho/35247507#35247507) answer may help. – HABO Jan 25 '19 at 16:49
  • You really need to provide a sample data set and clear requirements. – gbeaven Jan 25 '19 at 16:51
  • This may come as a shock to you, but we can't tell from a _picture_ of a space what character is present. Reasons not to use images are [here](http://meta.stackoverflow.com/a/285557/92546). It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. – HABO Jan 25 '19 at 19:03

1 Answers1

4

You could do

SELECT * 
FROM myTable
WHERE myColumn LIKE '% %' -- leading/trailing spaces and spacing within text
    OR myColumn LIKE CONCAT('%', CHAR(9), '%') -- tabs
dst3p
  • 1,008
  • 11
  • 25
  • That doesnt give the correct values. Some of them, we have it in the Prefix, Suffix – goofyui Jan 25 '19 at 16:40
  • 2
    Is there something specific you're trying to identify? This query will return results for any leading/trailing **spaces** as well as any spaces contained within the text of the column. It does not handle tab-spacing, however. – dst3p Jan 25 '19 at 16:45
  • I will have to run a report to generate all the records which has empty spaces in the trails or in the middle. Given query is not picking certain records. I am not sure, whether those empty spaces are created by tab space or not? From the report standpoint, we need list of records which has empty spaces on it – goofyui Jan 25 '19 at 16:49
  • You can easily modify the query to cover what cases you need. I'll edit and add a new case to cover tabs. – dst3p Jan 25 '19 at 16:52
  • 1
    for the tabs you could also do `where myColumn like concat('%', char(9), '%')` – PeterDeV Jan 25 '19 at 17:00
  • @PeterDeV I like it, and I can type it in markdown! – dst3p Jan 25 '19 at 17:01