Data problem (select)

Transact-SQL
1

Hello
I have a data problem:

I have a table that has two columns:

col1 (int)
col2 (nvarchar (255))

col1 col2

1 NAME1
2 NAME2
3 TUNISIA TELECOM

data tables that were imported from an Excel file and fueled by my application

I have a problem reading certain data (not all)

example:

select * from mytable Where col2 like 'TUNISIA%': it works
select * from mytable Where col2 like 'TUNISIA%': it does not work
select * from mytable Where col2 like 'TUNISIA t%': it does not work

The problem is in the space character

what do I have to do?

thank you

2

Check that it is really a SPACE or other unprintable character

Or use CLEAN() in excel to remove any before you import in.

3

how to know the character?

According to the display is a space

I even tried the ASCII code (255 alt), still nothing

The problem is that it's been that I imported data from EXCEL (myself) but I have not received this complaint from my client now

are there a way to see the hidden character?

4

use SUBSTRING() to extract 1 character and then use ASCII() to view the ascii code

5

I found the character, code 32

I wrote this query and it works

UPDATE mytable SET col2= REPLACE(col2, ' ', ' ')

(code 32
thank you khtan

6

ASCII 32 is SPACE and your replace statement basically replace space with space.

7

Use TRIM functions to ignore spaces while selecting. Such as
select * from mytable Where LTRIM(RTRIM(col2)) like 'TUNISIA%'