SQL Server Performance Forum – Threads Archive

Help rewrite Access SQL into SQL Stored Procedure

Hey! I have an Access database that i’m working to convert into a SQL Server DB, that ASP pages pull data from. I’m having problems converting my main Access query into a SQL Stored Procedure. Could someone please lend me a hand on getting this converted? Any help is extremely appreciated! Code purpose: Take a filename field and split it into multiple fields. Example Filename: C:inetpubftprootvuhmnen3p13092004,10182754,0051,95.jpg vuhmnen3 – 1 of 10 values p13092004 – date = 9/13/2004 10182754 – time = 10:18:27 (needs to only show 10:1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 0051 – license plate id (this can be 4 – 8 characters long) —-Access Query Code—- INSERT INTO tblFilePathDetail ( FileName, idxGarage, dtDay, dtMonth, dtYear, tmTime, ReadAccuracy, PlateID, dtDate, Weekday ) SELECT tblFilePaths.FilePath, Left(Mid([FilePath],InStr(1,[FilePath],"root")+5,9),InStr(1,Mid([FilePath],InStr(1,[FilePath],"root")+5,9),"")-1) AS garageid, Mid([FilePath],InStr(1,[FilePath],"p")+2,2) AS fnDay, Mid([FilePath],InStr(1,[FilePath],"p")+4,2) AS fnMonth, Mid([FilePath],InStr(1,[FilePath],"p")+6,4) AS fnYear, Mid([FilePath],InStr(1,[FilePath],"p")+11,4) AS fnTime, Mid([filepath],InStr(1,[filepath],".jpg")-2,2) AS fnAccuracy, Left(Mid([FilePath],InStr(1,[FilePath],"p")+20,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,InStr(1,Mid([FilePath],InStr(1,[FilePath],"p")+20,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,",")-1) AS fnPlate, [fnMonth] & "/" & [fnDay] & "/" & [fnYear] AS fnDate, Format([fnDate],"dddd") AS Weekday FROM tblFilePaths; —-End Code—-

Well, since we are here in SQL Server land, you don’t have instr() available. You might look for SUBSTRING and CHARINDEX or PATINDEX to get your job done. However, I would strongly recommend that you normalize your schema first while converting to SQL Server.
This one might also be interesting for you:
http://www.sommarskog.se/arrays-in-sql.html ———————–
–Frank
http://www.insidesql.de
———————–

Thanks for a quick response. The CHARINDEX seems to be what I need in order to extract my information. However, when combined with the LEFT or RIGHT function, I’m not able to get only the data I need.
FileName = E:inetpubftprootvuhmnen3p13092004,00021644,0051,95.jpg —code— LEFT(FileName,CHARINDEX(‘root’,FileName)+9) AS garageid —end code— I need to pull out only the value " vuhmnen3 ".
Regards, John Hobby
SQL Beginner
Tennessee

Ok, I think i’ve gotten most of my fields extracted. They were easy since they are the same size in all the FileNames. However, when i get to my PlateID the values here can vary from 4 characters to 8 characters. This is what I currently have that pulls it. Whats the best way to "trim" the " ,* " from this field? —code— SUBSTRING(FileName,48,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AS PlateID —end code— —results— 0081,9 —end results— Regards, John Hobby SQL Beginner Tennessee

Ok – here’s the latest i’m working with. —code— SELECT FileName, LEFT(FileName,SUBSTRING(FileName,48,7))AS PlateID —end code— But i’m getting the error message of: —error—
Syntax error converting the nvarchar value ‘ABC123,’ to a column of data type int.
—end error— Any thoughts??
Regards, John Hobby
SQL Beginner
Tennessee

It sounds like SUBSTRING(FileName,48,7) is returning a non integer value…? Cheers
Twan

It is. It’s returning something like " abc123,9 ". Regards, John Hobby
SQL Beginner
Tennessee

You will need to use charindex. You knwo where to start (since you’ve said said this is all fixed) but you will need to find where the next comma is. substring(filename, 48,charindex(‘,’,filename,4<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />-49) This should substring filename, starting in position 48, for a length of next comma position after field 48 – 49 (you would -49 so you dont get the comma) You’ll need to use the charindex (without the -49) to figure out where to start the xx.jpg part too.

Thanks Chris ! It’s working! Here is the final version: —code— SELECT FileName, substring(filename, 48,charindex(‘,’,filename,4<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />-4<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AS PlateID —end code— This pulls the plate from that location, so i don’t have to worry about the " ,98.jpg " part. Now, let’s see how fast this runs on 2.8 million records [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Regards, John Hobby SQL Beginner Tennessee

]]>