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!<br /><br />Code purpose: Take a filename field and split it into multiple fields.<br /><br />Example Filename: C:inetpubftprootvuhmnen3p13092004,10182754,0051,95.jpg<br /><br />vuhmnen3 – 1 of 10 values<br />p13092004 – date = 9/13/2004<br />10182754 – time = 10:18:27 (needs to only show 10:1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />0051 – license plate id (this can be 4 – 8 characters long)<br /><br /><br />—-Access Query Code—-<br /><br />INSERT INTO tblFilePathDetail ( FileName, idxGarage, dtDay, dtMonth, dtYear, tmTime, ReadAccuracy, PlateID, dtDate, Weekday )<br />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<br />FROM tblFilePaths;<br /><br />—-End Code—-<br />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?<br /><br />—code—<br /><br />SUBSTRING(FileName,48,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AS PlateID<br /><br />—end code—<br /><br />—results—<br /><br />0081,9<br /><br />—end results—<br /><br /><br />Regards,<br /><br />John Hobby<br />SQL Beginner<br />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.<br />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.<br /><br />substring(filename, 48,charindex(‘,’,filename,4<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />-49)<br /><br />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)<br /><br />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:<br /><br />—code—<br /><br />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<br /><br />—end code—<br /><br />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=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
]]>