April 13, 2007 at 9:21 am
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?
April 13, 2007 at 9:52 am
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)
April 13, 2007 at 10:27 am
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.
April 13, 2007 at 11:35 am
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"
April 13, 2007 at 11:43 am
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