Skip to main content
Solved

How to use SUBSTRING with a FLOAT value?

salma.zeped
Level 4

I need to take the first 8 values of a column to obtain the creation DATE. Then I need to take the remaining values for the TIME. I will then cast them as Date and Time for creating the EVENTTIME. The code below should have worked, but since it is a FLOAT data type I receive an error message on the Output.

 

Code:

SELECT ERSDA,ERSDA_TMSTP,SUBSTRING(ERSDA_TMSTP,0,8) AS "INFO1", SUBSTRING(ERSDA_TMSTP,9,4) AS "INFO2" FROM MCH1

 

Output:

Execution error: ERROR: Function catalog.substring(float, int, int) does not exist, or permission is denied for catalog.substring(float, int, int)

Best answer by antonio.perez12

Hi Salma,

 

Have you tried casting to cast the float value to string? Something like this should work:

 

SUBSTRING(CAST(ERSDA_TMSTP AS VARCHAR),0,8)

 

Kind regards,

View original

    S
    Level 4

    I presume you are using substring in data integration transformation scripts. substring (a,b,c) function does work in transformation scripts. Have you tried on any other field other than ERSDA_TMSTP. If still an issue must be a localized issue and support call may be needed.

    antonio.perez12
    Level 2

    Hi Salma,

     

    Have you tried casting to cast the float value to string? Something like this should work:

     

    SUBSTRING(CAST(ERSDA_TMSTP AS VARCHAR),0,8)

     

    Kind regards,

    salma.zeped
    Level 4
    antonio.perez12 wrote:

    Hi Salma,

     

    Have you tried casting to cast the float value to string? Something like this should work:

     

    SUBSTRING(CAST(ERSDA_TMSTP AS VARCHAR),0,8)

     

    Kind regards,

    Worked! Thank you Antonio 🙌

    salma.zeped
    Level 4
    sunny.thind wrote:

    I presume you are using substring in data integration transformation scripts. substring (a,b,c) function does work in transformation scripts. Have you tried on any other field other than ERSDA_TMSTP. If still an issue must be a localized issue and support call may be needed.

    Hi Sunny, I tested it with other columns and it worked fine. Seems like it was a data type error and the solution was to CAST it as VARCHAR. Appreciate your input!

    Reply


    Community Code of Conduct