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

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:

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

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.

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.

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?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.

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