1

I have several tex fields (varchar, nvarchar, ....) in a SQL Server 2012.

These fields are updated by the "users" pasting from different sources. And sometime, they paste a text that includes a "tabulator" space.

Obviously I can find spaces using:

SELECT * FROM table
WHERE field LIKE '%   %' /*3 spaces*/
OR field LIKE '%    %' /*4 spaces*/
OR field LIKE '%     %' /*5 spaces*/
OR field LIKE '%      %' /*6 spaces*/

But is there any specific way to find out "tabulator" spaces inside a text field?

Regards!

jorge_vicente
  • 358
  • 1
  • 5
  • 15
  • 1
    An answer may already exist here: http://stackoverflow.com/questions/12906949/how-eliminate-the-tab-space-in-the-column-in-sql-server-2008 – C B Mar 30 '17 at 16:34
  • Thanks, I was looking for using "tabulator" instead of "tab" :). – jorge_vicente Mar 30 '17 at 16:36

2 Answers2

2

Just in case you want to get rid of those extra spaces, here is a little technique Gordon demonstrated several weeks ago. It will handle ANY NUMBER of repeating spaces.

Declare @YourTable table (col varchar(100))
Insert Into @YourTable values
('Some    text    with extra spaces')

Select *
      ,Trimmed = replace(replace(replace(col,' ','><'),'<>',''),'><',' ')
 From  @YourTable

Returns

col                                 Trimmed
Some    text    with extra spaces   Some text with extra spaces

EDIT - To Update

Update YourTable
   set col = replace(replace(replace(col,' ','><'),'<>',''),'><',' ')
 Where col like '%  %'

No need to search for 3+ spaces, searching for double spaces will do the trick

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
1

To find them you can use charindex() with char(9).

select * 
from t
where charindex(char(9),col)>0;

rextester demo: http://rextester.com/OOX36348

SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • Thanks at all. I have tested it, and works correctly. I think that is the easiest way to find out where is a tab space in a column. (At least to find it; not modify). Very thanks at all! – jorge_vicente Apr 04 '17 at 06:00