February 16, 2006 at 7:40 am
The sample data below exists in a table with each line in a single varchar field.I need to extract the first value
(21770035,21770035,20090035,..) and associated dollar values at the end of each line (540.00, 473.49, 83.51, ....).
This is presently being done by exporting the data to a text file with a space
as the Field Separator and then reimporting the file to a new table with nine fields per line.Then running a query to
retrive the first and last field.
I would like to simplify the process with a query if possible.It is easy to retrive the first value
select Left(MyField,8) from MyTable
since this value is always eight characters. The length of the remaining values
and spaces may vary so I am not sure if this can be done with a query using SQl 2000
21770035 XXX LAW SCHOOL CAMPAIGN 0 0.00 0.00 0.00 540.00 0.00 540.00
21770035 XXX LAW SCHOOL CAMPAIGN 1395 3809.32 293.49 0.00 180.00 0.00 473.49
20090035 XXX OFFICE OF THE REGISTRAR 200 319.78 23.51 0.00 60.00 0.00 83.51
February 20, 2006 at 8:00 am
This was removed by the editor as SPAM
February 20, 2006 at 8:18 am
This could be done in T-SQL, but it will be a little cumbersome. YOur use of LEFT or SUBSTRING will get the first part. As to the remaining amounts, there should be come pattern, even if it's a pattern of spaces, that you can use to find the values.
Lookup CHARINDEX/PATINDEX for some ideas in BOL.
February 20, 2006 at 11:09 am
Steve
Thank you for your reply.I seem to remember using some combination of CHARINDEX/PATINDEX and Len
to solve a problem involving breaking up a single field into two fields in the past.
That involved searching on the space and triming excess spaces
to solve the problem of inconsistent number of spaces.
But since CHARINDEX/PATINDEX will find the "first occurrence of a pattern " rather than the eighth or ninth
I guess that would account for your statement of the T-Sql being a "little cumbersome".
I have not found a way of searching for the last space or the first space from the right which would make
this easy
But Thank you for your input
Lou
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply