locked
Extracting a number from a string RRS feed

  • Question

  • I'm trying to import data from access to sql. I would like to look in a string that has values like 1, 1 day, 1 week, net 5 and I would like to make it into an integer column in sql. What I would like to happen is that if the string contains week then the number in that string should be multiplied by 7 else it should just find the number within the string and pull it out. so that in the above examples it will save to sql as 1, 1, 7, 5. Is there a way to do this within the import wizard?


    Debra has a question

    Monday, May 30, 2016 7:14 PM

Answers

  • Hi Debra has a question,

    Supposing the records in Access DB are similar to the below records. 

    I suggest you firstly copy the table from Access DB to SQL DB with import wizard, then insert the data to destination table with following SQL statement.

    insert into dest_table(id,total_days) (select id,  case when RIGHT(total_days,4)='week' then convert(int,replace(total_days,' week',''))*7 else convert(int,replace(total_days,' day','')) end  as total_days from
    copy_table)

    Or using Derived Column Transformation in SSIS package. Reference the below package. 



    • Proposed as answer by Seif Wang Wednesday, June 8, 2016 11:04 AM
    • Edited by Seif Wang Wednesday, June 8, 2016 11:05 AM
    • Marked as answer by Eric__Zhang Friday, June 10, 2016 7:01 AM
    Thursday, June 2, 2016 11:20 AM

All replies

  • Hi Debra has a question,

    Have you tried with REPLACE in T-SQL it is very good function for your issue that you want to solve...

    REPLACE in T-SQL replaces all occurrences of a specified string value with another string value.

    Syntax of REPLACE:

    REPLACE ( string_expression , string_pattern , string_replacement )


    For example:

    SELECT REPLACE('abcdefghicde','cde','xxx');
    GO

    and the output-the new value of string will be:

    abxxxfghixxx

    More about REPLACE you can find on official MSDN documentation, so please take a look at here: https://msdn.microsoft.com/en-us/library/ms186862.aspx

    Hope this was helpful for you...

    Best regards!


    (If this was helpful for you, vote for it and propose it as an answer)

    Monday, May 30, 2016 8:33 PM
  • I don't know the number that will be in the string I just know there will be a number within string so I don't see how the replace will work. Also, the sql import wizard for importing access uses acess coding does Replace work in access coding?

    Debra has a question


    Tuesday, May 31, 2016 2:31 PM
  • Hi Debra has a question,

    Supposing the records in Access DB are similar to the below records. 

    I suggest you firstly copy the table from Access DB to SQL DB with import wizard, then insert the data to destination table with following SQL statement.

    insert into dest_table(id,total_days) (select id,  case when RIGHT(total_days,4)='week' then convert(int,replace(total_days,' week',''))*7 else convert(int,replace(total_days,' day','')) end  as total_days from
    copy_table)

    Or using Derived Column Transformation in SSIS package. Reference the below package. 



    • Proposed as answer by Seif Wang Wednesday, June 8, 2016 11:04 AM
    • Edited by Seif Wang Wednesday, June 8, 2016 11:05 AM
    • Marked as answer by Eric__Zhang Friday, June 10, 2016 7:01 AM
    Thursday, June 2, 2016 11:20 AM