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:
But when I ask for only that column alone with
SELECT client_x FROM table, I get:
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.
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?
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.
If you need to manipulate the data, you cannot use
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:
Some other db engines use brackets.
If the table contains
1234567, but you want to see
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 🙂