Compare Varchar with Char

SQL char to varchar comparison works, why?

Trailing spaces are ignored in string comparisons in SQL Server except for expressions on the right in a LIKE comparison.

SELECT CASE
         WHEN '168531' = '168531                ' THEN 'Y'
         ELSE 'N'
       END AS [=],
       CASE
         WHEN '168531' LIKE '168531                ' THEN 'Y'
         ELSE 'N'
       END AS [Like RHS],
       CASE
         WHEN '168531                ' LIKE '168531' THEN 'Y'
         ELSE 'N'
       END AS [Like LHS]    

Returns

=    Like RHS Like LHS
---- -------- --------
Y    N        Y

Compare varchar with char

Not a bug at all.

Casting the string literal 'ab ' to character, this is what you get:

a

Per documentation:

character without length specifier is equivalent to character(1).

'a'::character(1) will then be coerced to varchar (character varying) to test for equality with 'a'::varchar or 'ab'::varchar and yield TRUE or FALSE respectively.

Basically, there is hardly any good reason to use character at all. It's a legacy type that has outlived its usefulness. Just use text or varchar.

• Any downsides of using data type "text" for storing strings?

What's the difference between VARCHAR and CHAR?

VARCHAR is variable-length.

CHAR is fixed length.

If your content is a fixed size, you'll get better performance with CHAR.

See the MySQL page on CHAR and VARCHAR Types for a detailed explanation (be sure to also read the comments).

Compare two string char by char and highlight the different ones

Here is a working sample that you can turn into a function

Declare @S1 varchar(max) = 'Jack'
Declare @S2 varchar(max) = 'jake'

Declare @Return varchar(max) = ''

Select @Return = @Return + case when S2=S1 then S2
                       else '<font color=red>'+S2+'</font>'
                  end
 From (
        Select N
              ,S1 = substring(@S1,N,1)
              ,S2 = substring(@S2,N,1)
         From ( Select Top 1000 N=Row_Number() Over (Order By (Select NULL)) 
                 From master..spt_values n1, master..spt_values n2
              ) A
         Where N<=len(@S1) or N<=len(@S2)
      ) A
 Order by N

Select @Return

Results

ja<font color=red>k</font><font color=red>e</font>

What comparison problems can using the varchar type instead of the char type avoid?

The basic issue is that char will pad the value with spaces and this can lead to some surprising and inconsistent results.

Here we see Postgres retains the trailing space.

test=> create table foo ( c char(10), v varchar(10) );
CREATE TABLE

test=> insert into foo values ('foo', 'foo');
INSERT 0 1

test=> select * from foo;
     c      |  v  
------------+-----
 foo        | foo

test=> select concat(c, '>'), concat(v, '>') from foo where c = 'foo     ';
   concat    | concat 
-------------+--------
 foo       > | foo>

But MySQL does not unless PAD_CHAR_TO_FULL_LENGTH is set.

mysql> create table foo ( c char(10), v varchar(10) );

mysql> insert into foo values ('foo', 'foo');

mysql> select * from foo;
+------+------+
| c    | v    |
+------+------+
| foo  | foo  |
+------+------+

mysql> select concat(c, '>'), concat(v, '>') from foo where c = 'foo     ';
+----------------+----------------+
| concat(c, '>') | concat(v, '>') |
+----------------+----------------+
| foo>           | foo>           |
+----------------+----------------+

mysql> set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

mysql> select concat(c, '>'), concat(v, '>') from foo where c = 'foo     ';
+----------------+----------------+
| concat(c, '>') | concat(v, '>') |
+----------------+----------------+
| foo       >    | foo>           |
+----------------+----------------+

The PostgreSQL documentation outlines several issues.

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way.

...trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character. In collations where whitespace is significant, this behavior can produce unexpected results; for example SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even though C locale would consider a space to be greater than a newline

Trailing spaces are removed when converting a character value to one of the other string types.

Storage engines have improved such that there's little reason to use char anymore.

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

One case where char might be justified is to store very small, fixed size strings. For example, ISO 2 character country codes might be stored as char(2). But the performance difference is unlikely to be noticed on such small strings.

char is a headache best avoided.

Comparing every char in a string to a constant

strcmp() is for comparing strings. To compare characters, you can use == operator.

Also note that sizeof is not for getting length of strings but getting number of bytes used for the type. In this case it is used for char array, so it may work according to what you want to do because sizeof(char) is defined to be 1 and therefore the number of bytes will be equal to the number of elements. Note that the terminating null-character and unused elements after that will added to the count if they exists. To get the length of string, you should use the strlen() function.

int i;
const char perc = '%'; /* use char, not char* */
char mystr[7] = "hell%o";
int len = strlen(mystr); /* use strlen() to get the length of the string */

for(i=0;i<len;i++){
        if(mystr[i] != perc){ /* compare characters */
            printf("%d",i);
        }

How to Compare a String with a Char

The first thing I would say to any of my junior devs is to not use the word "try" as a method name, because try is a reserved keyword in java.

Secondly think that there are a few things which you need to consider in your method.

If you compare things of two different types they will never be the same.
A String can be null.
How long the string is.
The first char.

I would write the method like :

public boolean isSame() {
    if (s != null && s.length() == 1 { 
        return s.charAt(0) == c;
    }
    return false;
}

Can you compare varchar field one of table and char field of another table

yes you can it.

first table have;

VARCHAR_COLUMN varchar(10)

second table have;

CHAR_COLUMN char(10)

you can use;

CHAR_COLUMN = VARCHAR_COLUMN

or convert;

Convert(varchar(10), CHAR_COLUMN) = VARCHAR_COLUMN

VARCHAR text is not equal to Char text while comparing with LIKE operator in sql server

@A is of type CHAR(20), so it's value is padded on the right with spaces to fill it out to a length of 20.

The Len() function excludes trailing spaces, so both @A and @B show a length of 1 even though @A has been padded out with spaces.

For comparisons, SQL Server follows ansi standards which require padding of strings to the same length before comparing, so @A and @B show as equal.

The one exception to this is the LIKE predicate, which does not pad the strings to the same length if the trailing spaces are on the right.

Ref: How SQL Server Compares Strings with Trailing Spaces

Best way to compare VARCHAR2 with CHAR

As the table1.value column is indexed, you don't want to manipulate that for the comparison as that would prevent the index being used. So you'll need to modify the value you're looking up:

SELECT table1.ID FROM table1 WHERE table1.VALUE = RPAD('123-45', 12)

Oracle will do that implicitly with the query you showed though, and will still use the index. And the same if you're joining the tables, but whether you pad or trim during the join depends on which table is the driver:

SELECT table1.ID, table2.ID
FROM table1
JOIN table2 ON table2.value = RTRIM(table1.value)
WHERE table1.VALUE = RPAD('123-45', 12)

Or:

SELECT table1.ID
FROM table2
JOIN table1 ON table1.value = RPAD(table2.value, 12)

---

Related Topics