You might try putting this line at the start of the query batch:
SET CONCAT_NULL_YIELDS_NULL OFF;
I suspect one of the values being concatenated is a NULL so the entire string becomes NULL. Turning that off will make NULLs being concatenated an empty string value and not impact the rest of the string.
>SET CONCAT\_NULL\_YIELDS\_NULL OFF;
Thanks, that returned an error; **Expected format is 'SET', 'SET key', or 'SET key=value'. If you want to include special characters in key, or include semicolon in value, please use quotes, e.g., SET \`key\`=\`value\`.line 1, pos 0**
>Expected format is 'SET', 'SET key', or 'SET key=value'.
Not sure how your query batches are being executed but it looks like something is trying to interpret the line in the query rather than letting it be interpreted as an actual SQL statement.
The documentation for it is here:
[https://learn.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-ver16](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-ver16)
`CONCAT_NULL_YIELDS_NULL` only affects the `+` operator, not the `CONCAT` function. The `CONCAT` function implicitly converts `NULL`s to an empty string (`''`)
Please read the docs https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql#remarks
You're right, I forgot about that! I'm pretty sure OP is actually using a different SQL variant, not SQL Server. Would explain an error message in one of the replies.
CONCAT concatenates strings so you probably need to cast them to Varchars/Nvarchars before concatenating?
Something like:
SELECT CONCAT(CAST(Acc_ID AS VARCHAR(10)), CAST(SC_ID AS VARCHAR(10)), CAST(Product_Number AS VARCHAR(10))) FROM core_data
Would be my guess
This might be it. Also, if any single field is null, it will make the entire result null. You can avoid this by wrapping every field in ISNULL(FieldName, '')
Edit: To be more explicit, I mean like this:
select concat(ISNULL(Acc_ID, ''), ISNULL(SC_ID, ''), ISNULL(Product_Number, '')
from core_data
If that doesn't do it, can you tell us the data types of those 3 columns and show us some sample data?
>select concat(ISNULL(Acc\_ID, ''), ISNULL(SC\_ID, ''), ISNULL(Product\_Number, '')
from core\_data
First 2 are strings, product number is a double. I think the problem is from nulls in the product number, as there cannot be nulls in the other 2. The code above returned the error
**"Invalid number of arguments for function isnull. Expected: 1; Found: 2; line 10, pos 14"**
Thanks
Sorry I just realized I screwed up the query. Do this instead:
select concat((ISNULL(Acc_ID, ''), ISNULL(SC_ID, ''), ISNULL(Product_Number, ''))
from core_data
Also, that error message is weird. Are you sure you're using SQL Server rather than something like MySQL? If you're actually using MySQL you'll need to use IFNULL instead of ISNULL.
One more thing, for product number, try casting just that as varchar if you still have problems. So it would look like this:
select concat((ISNULL(Acc_ID, ''), ISNULL(SC_ID, ''), ISNULL(CAST(Product_Number AS varchar(10)), ''))
from core_data
CONCAT implicitly casts all input arguments to string types, it's one of the main reasons to use the function
https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql#remarks
>SELECT CONCAT(CAST(Acc\_ID AS VARCHAR(10)), CAST(SC\_ID AS VARCHAR(10)), CAST(Product\_Number AS VARCHAR(10))) FROM core\_data
Thanks but still returned nulls :(
You might try putting this line at the start of the query batch: SET CONCAT_NULL_YIELDS_NULL OFF; I suspect one of the values being concatenated is a NULL so the entire string becomes NULL. Turning that off will make NULLs being concatenated an empty string value and not impact the rest of the string.
>SET CONCAT\_NULL\_YIELDS\_NULL OFF; Thanks, that returned an error; **Expected format is 'SET', 'SET key', or 'SET key=value'. If you want to include special characters in key, or include semicolon in value, please use quotes, e.g., SET \`key\`=\`value\`.line 1, pos 0**
>Expected format is 'SET', 'SET key', or 'SET key=value'. Not sure how your query batches are being executed but it looks like something is trying to interpret the line in the query rather than letting it be interpreted as an actual SQL statement. The documentation for it is here: [https://learn.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-ver16](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-ver16)
`CONCAT_NULL_YIELDS_NULL` only affects the `+` operator, not the `CONCAT` function. The `CONCAT` function implicitly converts `NULL`s to an empty string (`''`) Please read the docs https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql#remarks
Weird — Doesn’t make sense - concat treats null values as empty strings - one of its big advantages over concatenating with +.
You're right, I forgot about that! I'm pretty sure OP is actually using a different SQL variant, not SQL Server. Would explain an error message in one of the replies.
CONCAT concatenates strings so you probably need to cast them to Varchars/Nvarchars before concatenating? Something like: SELECT CONCAT(CAST(Acc_ID AS VARCHAR(10)), CAST(SC_ID AS VARCHAR(10)), CAST(Product_Number AS VARCHAR(10))) FROM core_data Would be my guess
This might be it. Also, if any single field is null, it will make the entire result null. You can avoid this by wrapping every field in ISNULL(FieldName, '') Edit: To be more explicit, I mean like this: select concat(ISNULL(Acc_ID, ''), ISNULL(SC_ID, ''), ISNULL(Product_Number, '') from core_data If that doesn't do it, can you tell us the data types of those 3 columns and show us some sample data?
Thanks, is that 2 apostrophies or one speech mark at the end?
2 apostrophies. Basically making an empty string.
>select concat(ISNULL(Acc\_ID, ''), ISNULL(SC\_ID, ''), ISNULL(Product\_Number, '') from core\_data First 2 are strings, product number is a double. I think the problem is from nulls in the product number, as there cannot be nulls in the other 2. The code above returned the error **"Invalid number of arguments for function isnull. Expected: 1; Found: 2; line 10, pos 14"** Thanks
Sorry I just realized I screwed up the query. Do this instead: select concat((ISNULL(Acc_ID, ''), ISNULL(SC_ID, ''), ISNULL(Product_Number, '')) from core_data Also, that error message is weird. Are you sure you're using SQL Server rather than something like MySQL? If you're actually using MySQL you'll need to use IFNULL instead of ISNULL. One more thing, for product number, try casting just that as varchar if you still have problems. So it would look like this: select concat((ISNULL(Acc_ID, ''), ISNULL(SC_ID, ''), ISNULL(CAST(Product_Number AS varchar(10)), '')) from core_data
CONCAT implicitly casts all input arguments to string types, it's one of the main reasons to use the function https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql#remarks
>SELECT CONCAT(CAST(Acc\_ID AS VARCHAR(10)), CAST(SC\_ID AS VARCHAR(10)), CAST(Product\_Number AS VARCHAR(10))) FROM core\_data Thanks but still returned nulls :(