Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-06 : 20:31:28
|
| Mitch writes "I've searched for an answer to this question for a while with no luck. I receive a non-delimited text document, along with a record layout, weekly that contains three types of records. The three types are distinguished by the first character of the record. After that, the three types of records have different layouts. They are all 500 characters in length, with trailing spaces after the data.My thought was to load the text doc into a one column table that looks like this: CREATE TABLE dbo.temp_text_test (data_field text NOT NULL)go Then I could write a cursor that would go through and look at the first character of each record, parse it out according to the appropriate record layout using the substring function, and insert the parsed record into one of three tables depending on the first character.To test before I went through the effort of writing the cursor, I inserted a dummy record into the above table: 'XXXXXxxxxxXXXXXxxxxxXXXXXxxxxx'Then I did the following:SELECT SUBSTRING(data_field, 1, 10), SUBSTRING(data_field, 11,20)FROM DBTMP..temp_text_test It returns the following error: "The data type 'text' is invalid for the substring function. Allowed data types are: CHAR/VARCHAR and BINARY/VARBINARY" Is there another function that will parse out a text field? I can't seem to find it if there is.I've been using a series of queries to do this in Access, and it works OK, but I don't like the process and was hoping do it more efficiently in a SP in SQL. If I'm going the wrong route, please set me straight. By the way, we're using SQL Server 6.5.Thanks, Mitch" |
|
|
|
|
|
|
|
Parsing Text Field w/no delimeters