In MySQL query, VARCHAR(100) gets output as "[partnumber]" although in a query with more columns, it is still shown as 'p-12345'

All we need is an easy explanation of the problem, so here it is.

This is a bad question since I only made a mistake in selecting by chance a client that had garbage data and must have changed the where condition during tests, and it was also a problem of a join that I did not even mention in the question that led to so many of the strange [partnumber] entries that the limit 10 query showed only those. So that I thought the whole column would have this value. I am not sure whether I should delete it. It might also help someone else who has garbage data without knowing about it. You may vote to close the question if you think it has wasted your time. For I see myself that this can be a waste of time reading.

In a normal SELECT p.* FROM table query with many columns, the output shows values in a needed column, every number shows up, showing the column with the partnumber (a partially numeric number like p-12345) only:

In MySQL query, VARCHAR(100) gets output as "[partnumber]" although in a query with more columns, it is still shown as 'p-12345'

But when I ask for only that column alone with SELECT client_x FROM table, I get:

In MySQL query, VARCHAR(100) gets output as "[partnumber]" although in a query with more columns, it is still shown as 'p-12345'

UPDATE:

I can see the values of that column now!! They are not showing [partnumber] anymore in each row, but instead, there are numbers and strings and empty values as well in it.

In MySQL query, VARCHAR(100) gets output as "[partnumber]" although in a query with more columns, it is still shown as 'p-12345'

I had the where condition on the client in it, and the value in that client is always [partnumber]. It is garbage only in that client.

UPDATE end

The column data type is varchar(100). It should be treated as a normal string.

In MySQL query, VARCHAR(100) gets output as "[partnumber]" although in a query with more columns, it is still shown as 'p-12345'

I use DBeaver, but that should not play a role.

If I concatenate the column with some string, the value appears as an empty string:

SELECT CONCAT("test", client_x) FROM table

returns just "test" as values.

How can I avoid this [partnumber] output in the column output and show the part number string instead, like it is shown when I just run SELECT * FROM table?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you need to manipulate the data, you cannot use SELECT *.

If you are seeing [partnumber] coming from the table, then GIGO.

Note: MySQL does not use brackets. If the column is named `partnumber then either of these will work, but brackets won’t:

partnumber
`partnumber`

Some other db engines use brackets.

If the table contains 1234567, but you want to see p-1234567:

SELECT  CONCAT('p-', partnumber) AS partnumber, ...

If the table contains p-1234567 and you want to display 1234567, here is a way assuming the first 2 characters need to be stripped:

SELECT  MID(partnumber, 3) AS partnumber, ...

If neither of those answer your question, please rephrase it.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply