Getting an error when using CONCAT in BigQuery

Question

I'm trying to run a query where I combine two columns and separate them with an x in between.

I'm also trying to get some other columns from the same table. However, I get the following error.

Error: No matching signature for function CONCAT for argument types: FLOAT64, FLOAT64. Supported signatures: CONCAT(STRING, [STRING, ...]); CONCAT(BYTES, [BYTES, ...]).

Here is my code:

SELECT
  CONCAT(right,'x',left),
  position,
  numbercreated,
  Madefrom
FROM
  table
WHERE
Date = "2018-10-07%"

I have tried also putting a cast before but that did not work.

SELECT Concast(cast(right,'x',left)), position,... SELECT Concast(cast(right,'x',left)as STRING), position,...

Why am I getting this error? Are there any fixes? Thanks for the help.

Gordon Linoff · Accepted Answer · 2019-04-10 10:35:37Z

2

You need to cast each value before the concat():

SELECT CONCAT(CAST(right as string), 'x', CAST(left as string)),
       position, numbercreated, Madefrom
FROM table
WHERE Date = '2018-10-07%';

If you want a particular format, then use the FORMAT() function.

I also doubt that your WHERE will match anything. If Date is a string, then you probably want LIKE:

WHERE Date LIKE '2018-10-07%';

More likely, you should use the DATE function or direct comparison:

WHERE DATE(Date) = '2018-10-07'

or:

WHERE Date >= '2018-10-07' AND
      Date < '2018-10-08'

answered Apr 10, 2019 at 10:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmed Over a year ago

Thank you. Solved the problem!

Mikhail Berlyant · Accepted Answer · 2019-04-11 06:03:02Z

Another option to fix your issue with CONCAT is to use FROMAT function as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1.01 AS `right`, 2.0 AS `left`
)
SELECT FORMAT('%g%s%g', t.right, 'x', t.left)
FROM `project.dataset.table` t

result will be

Row f0_  
1   1.01x2

Note: in above specific example - you could use even simpler statement

FORMAT('%gx%g', t.right, t.left)

You can see more for supporting formats

Few recommendations - try not to use keywords as a column names/aliases. If for some reason you do use - wrap such with backtick or prefix it with table name/alias

Yet another comment - looks like you switched your values positions - your right one is on left side and left one is on right - might be exactly what you need but wanted to mention

Pol Ortiz · Accepted Answer · 2019-04-15 10:08:28Z

0

Try like below by using safe_cast:

SELECT
    CONCAT(SAFE_CAST( right as string ),'x',SAFE_CAST(left as string)),
    position,
    numbercreated,
    Madefrom
FROM
    table
WHERE
    Date = '2018-10-07'

edited Apr 15, 2019 at 10:08

Pol Ortiz

4826 silver badges15 bronze badges

answered Apr 10, 2019 at 10:35

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Getting an error when using CONCAT in BigQuery

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related