validate value... 1

[tran008]

I need the following to be validate. the field contain 11 character (X8656787659) How ever, some of the data is just only 10. I want to select which field have only 10 character no include space. I have look up LEN, right function, but some how it is not return right value. The problem is the some of field have space at the end, eg.
X8656787659
X876000080
C653566
X4566777659
How do I check the data to see if the have a space at the end?

 

[SQLSister]

just use rtrim(fieldname) to trim away any spaces


Questions about posting. See faq183-874

 

[tran008]

Hi sis,

I did try that, but it doesn't return the right result. Could the space may be somesort of char which not display right? If so, which function or syntax to find out if the char is the normal abc..123 data?

thanks

 

[gmmastros]

The Len function ignores trailing spaces.

[COLOR=blue]declare[/color] @Test [COLOR=blue]Char[/color](20)

[COLOR=blue]Set[/color] @Test = [COLOR=red]'yes    '[/color]
[COLOR=blue]Select[/color] Len(@Test) [COLOR=green]-- Returns 3
[/color]
[COLOR=blue]Set[/color] @Test = [COLOR=red]' yes'[/color]
[COLOR=blue]Select[/color] Len(@Test) [COLOR=green]-- Returns 4[/color]

So... you either have spaces at the beginning, or other non-printable characters at the beginning/end.

If you know what the 'bad data' is, you could try running this query to see what the offending ASCII value is.

Select ascii(SubString(ColumnName, 1, 1)) As Char1,
       ascii(SubString(ColumnName, 2, 1)) As Char2,
       ascii(SubString(ColumnName, 3, 1)) As Char3,
       ascii(SubString(ColumnName, 4, 1)) As Char4,
       ascii(SubString(ColumnName, 5, 1)) As Char5,
       ascii(SubString(ColumnName, 6, 1)) As Char6,
       ascii(SubString(ColumnName, 7, 1)) As Char7,
       ascii(SubString(ColumnName, 8, 1)) As Char8,
       ascii(SubString(ColumnName, 9, 1)) As Char9,
       ascii(SubString(ColumnName, 10, 1)) As Char10
From   tableName
Where  ColumnName Like '%C653566%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LNBruno]

Post your query so we can see what it is, exactly, that you're trying to do...

< M!ke >
"Believe, but verify.

 

[SQLBill]

Another thing....what datatype is the column - CHAR or VARCHAR?

-SQLBill

Posting advice: FAQ481-4875