SELECTQUERY, make 1 column into 2 columns

[jeroenvanveghel]

Hi guys and girls!

I'm new at mysql so please help me out.

 

I have 1 table, consisting of three rows:

number, description, relationNumber.

 

and i want to query the following on this table:

SELECT number, description, 

CONCAT( MOD( relationNumber , 100) , SIGNED)) AS relationCode, 
CONCAT (MOD( ( relationNumber / 100) , 100 ) , SIGNED ) as relationCodeExtra

FROM erwin.relatiecode r;

 

anyway this is giving me the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), CONCAT (MOD( (relationNumber  / 100) , 100 ) , SIGNED ) as rel' 

 

So what i'm trying to accomplish is to split one column into 2 columns.

Is this possible in MYSQL? Because my code isn't flexible enough.

 

Thanks for reading my post! And double thanks if you can give me an answer!

[kickstart]

Hi

 

CONCAT is a string function and not sure what you are trying to use it for there. Also you have some extra brackets.

 

Splitting a column into 2 is very possible.

 

All the best

 

Keith

[jeroenvanveghel]

ok and how is it possible to split a column in two then?

 

(Concat is indeed not needed, copied it from another query i had  )

[mrqpro]

Why don't you try this?

 

SELECT number, description,

MOD( relationNumber , 100)  AS relationCode,

MOD(relationNumber / 100 , 100 )  as relationCodeExtra

 

FROM erwin.relatiecode r;

 

Not need to use CONCAT function in this case.

 

http://dacloi.com

[jeroenvanveghel]

Why don't you try this?

SELECT number, description, 
MOD( relationNumber , 100)  AS relationCode, 
MOD(relationNumber / 100 , 100 )  as relationCodeExtra

FROM erwin.relatiecode r;

 

That works actually, but the problem is the integer is not signed then. I need that because i want 'whole'  numbers only (don't know the phrase to say it in English).

So when i rewrite it to this:

SELECT number, number,
MOD( (relationNumber , 100), SIGNED)  AS relationCode,
MOD( (relationNumber / 100 , 100 ), SIGNED)  AS relationCodeExtra

FROM erwin.relatiecode r;

I receive the following error:

Operand should contain 1 column(s)          errorNr: 1241.

Not sure why this happens.

[jeroenvanveghel]

Anyway, i solved it in my code now because i couldn't get it to work within the query.

Thanks for your help!

[kickstart]

That works actually, but the problem is the integer is not signed then. I need that because i want 'whole'  numbers only (don't know the phrase to say it in English).

 

For future reference, an integer is a whole number.

 

If you just want the integer part then you can use FLOOR, CEIL or ROUND Mysql functions.

 

All the best

 

Keith