Returning the Distinct First Character of a Field (Mysql)

Returning the DISTINCT first character of a field (MySQL)

Sorry to do this, but I figured out exactly what I needed to do just now.

SELECT DISTINCT LEFT(name, 1) FROM mydatabase

This returned a list of the first, distinct, single characters that each row in the column started with. I added changed it to the following to get it the list in alpha-numeric order:

SELECT DISTINCT LEFT(name, 1) as letter FROM mydatabase ORDER BY letter

Works like a charm.

Get Distinct First Character, Return Entire Column

If you want only one row per "something", then row_number() seems like the obvious candidate for solving the problem:

select t.*
from (select t.*,
             row_number() over (partition by left(majName, 1) order by newid()) as seqnum
      from @VTest t
     ) t
where seqnum = 1;

Note that this uses SQL Server conventions for things like randomizing the row. Your code looks like SQL Server code.

Query to return the distinct first characters for given text column

SELECT DISTINCT(SUBSTR(name, 1, 1)) AS indexChar
FROM myTable
ORDER BY indexChar

You can find a list of all SQLite functions at Core Functions

Select distinct first words of a field

You can reverse the string to find the first space " "

SQL Demo

SELECT DISTINCT RTRIM(REVERSE(SUBSTRING(REVERSE(`model`),LOCATE(" ",REVERSE(`model`)))))
FROM Table1

OUTPUT

How do I select a unique list of first characters [MySQL]

LEFT(str, 1) is supposed to the leftmost character, not the leftmost byte. This means the query is doing what you want, even if the first character is a multibyte character.

I'm guessing the � sign emerges later, due to a connection/encoding/font/rendering problem. Try

SELECT LENGTH(LEFT(T1.Name, 1)) AS charLength

LENGTH returns how many bytes a string takes up, so if this query gives you any results of 2 or more, this means that LEFT() is indeed returning multibyte characters and your problem lies beyond the query itself.

If you are executing the query at the command line, maybe your terminal cannot render the characters, or otherwise they are getting mangled somewhere else. If you are using a scripting language, try to use that language's string length, and ord() functions, to help find out what's going on.

EDIT: Since you are using PHP, try this:

//Store a character returned from the database in $unicodechar
$unicodechar = $row[0];

//Now print out the value of each byte in the character
for($i = 0; $i < strlen($unicodechar); $i++)
{
    echo '0x' . dechex(ord($char[$i])) . ' ';
}
echo '\n';

If for example the result is this character then you should get "0xC4 0x9E". If you do indeed get this kind of thing, then PHP is getting the multibyte character properly, and the problem is either in the encoding of the web page itself (see this W3C page) or the browser/font cannot render that particular character.

change the first character in a field

UPDATE customers_basket 
SET products_id = CONCAT(
                   REPLACE(
                      LEFT(products_id,1), 'U', 'S'),      
                      SUBSTRING(products_id, 2, CHAR_LENGTH(products_id)
));

You are trying to add characters together, e.g.

select 'c' + 'a';
+-----------+
| 'c' + 'a' |
+-----------+
|         0 |
+-----------+

How to select first four letter of the column Distinct by select query in mysql?

Use "group by" instead of distinct:

select LEFT(partyCode,4) as code from parties group by code;

Distinct works too:

select distinct LEFT(partyCode,4) as code from parties;

So I don't quite know where your problem is.

Return first character SQL Derby

select substr(table.column,1,1) 
from table
order by table.column

select distinct rows without special character in the first occurrence in mysql

select distinct trim(col) from your_table

MySQL Select Query - Get only first 10 characters of a value

Using the below line

SELECT LEFT(subject , 10) FROM tbl 

MySQL Doc.

---

Related Topics