CAST varchar to int

  • I am creating a table from a text file. It is answers from a survey done on a scantron.

     

    A row of data in the text file would look like this

    02245A555115555155

     

    I use bulk insert to being it into sql as a varchar.

    I want to divide up the row and insert it into a new table for reporting. My problem is the answers (everything after the A) needs to be an int. I can't CAST(SUBSTRING (data,16,1) as int) AS ColumnName

    I was wondering if it was possible and senseible to create a user defined function to select the substring and return an int.

    Is this possible?

  • SELECT

        CAST(LEFT(DataRow, CHARINDEX('A', DataRow) - 1) AS int) AS QuestionNo

        ,CAST(SUBSTRING(DataRow, CHARINDEX('A', DataRow) + 1, 8000) AS bigint) AS AnswerNo

    FROM (

            SELECT '02245A555115555155'

        ) AS YourTable (DataRow)

     

  • Thanks,

     

     I have found a problem in the file were there are characters like 'S' where a number is supposed to be. That causes this to fail.

  • SELECT

        CAST(LEFT(DataRow, PATINDEX('%[^0-9]%', DataRow) - 1) AS bigint) AS QuestionNo

        ,CAST(SUBSTRING(DataRow, PATINDEX('%[^0-9]%', DataRow) + 1, 8000) AS bigint) AS AnswerNo

    FROM (

            SELECT '02245A555115555155' AS DataRow

        ) AS YourTable

     


    N 56°04'39.16"
    E 12°55'05.25"

  • I've got it working. I have added code before the insert that checks for ?'s and just deletes those rows and it runs fine. Thanks for everyone's help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply