CODEWITHSUNDEEP
mysqlreplaceconcatenation
OldWest
OldWest

Reputation: 2385

REPLACE() CONCAT() & SUBSTR() for inserting one character in between concatenated fields

I continue to get an SQL Syntax error. I tested all fields with prefixes etc and all data is loading OK, but when I trying to use my replace concat, won't stop throwing errors.. Any idea why this would happen?

I also need the white space removed from the nums table.

Data input format:
nums: 21 0440N 0110W 026
state: CA
part: AA

Final results should look like:
CA21T0440N0110W026AA (note the white space is also removed.)

replace(
           concat(
               b.state,
                   substr(b.nums, 0, 3),
               'T',
                   substr(b.nums, 3),
               b.part
                  )
      ) AS AllInfoConcat,

Upvotes: 1

Views: 7936

Answers (1)

Jon
Jon

Reputation: 437336

Where are the two other parameters for REPLACE? This should work:

REPLACE(
  CONCAT(b.state, substr(b.nums, 0, 3), 'T', substr(b.nums, 3), b.part),
  ' ',
  '') AS AllInfoConcat,

Upvotes: 2

Related Questions