SQL Select Command - Partial Variable String

[lcottrell]

I'll start off by saying I'm not very experienced in writing SQL commands. I am familiar with vbscript though, and I can do a similar thing in that, but can't seem to get it to work in SQL.

What I need is to use Excel to connect to an SQL database and pull down information periodically, which I've been able to mostly do successfully. The problem now is that the information in some of the fields has extra information that makes it hard to read for the end user that needs this information. For example, the raw data in one field is "1234567.8901-1^101010-1111^L", but I want to configure Microsoft Query via the SQL Select command to cut out part of that string and only show the "101010-1111". The other thing is that this string is not always 11 characters as in this example. It might be only 10 sometimes too. I have managed to get it to cut the first part, but have been unsuccessful in trimming parts off both the beginning and the end.

Here is what I have for the command that pulls this specific column of data:

SUBSTRING(accession.id,CHARINDEX('^',accession.id) +1, (CHARINDEX('L',accession.id)-1) - (CHARINDEX('^',accession.id) +1)) AS Accession

Now I have done something similar to this in vbscript, where I calculated the length for each entry and it would determine the correct number of characters to return. When I run it with this command to return it to Excel, it gives me the error "[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the substring function". This happens when I right click on the Query that I have saved as a .dqy file and choose "Open in Microsoft Office Excel". If I choose "Edit With Microsoft Query", it will actually open the tables and the query and the results in the column show the correct data that I want. I just can't get it to where I can open that in Excel.

Any ideas anyone?

[iburyak]

Did you try to use this where condition ?

[PHP]where accession.id like '%^%^L"[/PHP]

It looks like that some of the records don't find something in a string and you get something like this:

[PHP]SUBSTRING(accession.id, 0, -1) [/PHP]

and this is why server complains.

[iburyak]

Try to do this:


[PHP]Select accession.id, CHARINDEX('^',accession.id) + 1 Start, (CHARINDEX('L',accession.id)-1) - (CHARINDEX('^',accession.id) +1) length
from table_name
order by 3[/PHP]

See if you'll get some answers to your questions.

Good Luck.

[lcottrell]

I tried to implement what you suggested, but it gave me an error stating "Line 1: Incorrect syntax near 'Start'." Here is what my entire query command looks like after incorporating your suggestion when I right click on the .dqy file and do Edit with Notepad. Hopefully this will help you understand what I'm trying to do. As I said, I don't know much about the SQL querying...this is just what was there by default after setting up the query in Excel and then what I've changed for the one accession.id column but have been unsuccessful in limiting the string. Also, the accession.id column ALWAYS has data, and is always in the format that I mentioned. It won't vary other than the string length.

XLODBC
1
DRIVER=SQL Server;SERVER=server;UID=user1;APP=Microsoft Office 2003;WSID=myworkstation;DATABASE=DB1;Trusted_Connection=Yes
SELECT accession.complete_date, SUBSTRING(accession.id, CHARINDEX('^',accession.id) + 1 Start, (CHARINDEX('L',accession.id)-1) - (CHARINDEX('^',accession.id) +1) length) AS Accession, patient.ssn, patient.last, patient.first, procedures.description, report_to_user.user_id  FROM DB1.dbo.accession accession, DB1.dbo.encounter encounter, DB1.dbo.patient patient, DB1.dbo.procedures procedures, DB1.dbo.report_to_user report_to_user  WHERE encounter.patient_id = patient.id AND report_to_user.report_id = accession.report_id AND procedures.procedure_code = accession.procedure_code AND accession.encounter_id = encounter.id  ORDER BY accession.complete_date
 
 
complete_date    id    ssn    last    first    description    user_id
 

Try to do this:


[PHP]Select accession.id, CHARINDEX('^',accession.id) + 1 Start, (CHARINDEX('L',accession.id)-1) - (CHARINDEX('^',accession.id) +1) length
from table_name
order by 3[/PHP]

See if you'll get some answers to your questions.

Good Luck.

[iburyak]

What I suggested is not to incorporate into your query in no way.
It is just to overview your data and make sure all records have valid pattern and both [^] and [^L] are present in each record.
I thought if you would be able overview it you would be able to see that data does have invalid records and it is a reason your query doesn't work.

Your original query looked correct just it looks like data is not always good to find a substring.

Also I suggested to use where condition to eliminate records with data that doesn’t satisfy this pattern.

[PHP] where accession.id like '%^%^L" [/PHP]

[lcottrell]

iburyak,

I'm sorry I misunderstood what you were telling me. I put my original SELECT statement back in, and added your WHERE command in and it worked! That made me very curious, since I couldn't see why it would make a difference. The sample data I was looking at was over 37,000 rows. I did a more in depth check of the accession.id column and I did find a row that had "temporary <date>" in it. I was wrong in my statement that it will always have that data format in that column.

Thank you very much for your help!

What I suggested is not to incorporate into your query in no way.
It is just to overview your data and make sure all records have valid pattern and both [^] and [^L] are present in each record.
I thought if you would be able overview it you would be able to see that data does have invalid records and it is a reason your query doesn't work.

Your original query looked correct just it looks like data is not always good to find a substring.

Also I suggested to use where condition to eliminate records with data that doesn’t satisfy this pattern.

[PHP] where accession.id like '%^%^L" [/PHP]

[iburyak]

This is why I suggested statement below so you would be able to overview invalid values in a column. Start and end it is what you were about to pass to Substring function and it couldn't handle numbers less then 1.

[PHP]Select accession.id, CHARINDEX('^',accession.id) + 1 Start, (CHARINDEX('L',accession.id)-1) - (CHARINDEX('^',accession.id) +1) length
from table_name
order by 3 [/PHP]

It is common problem and I just know this from experience that data is not always perfect... :)


I am glad it helped.
Good Luck.