Parsename question...yet again

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parsename question...yet again

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-28 : 22:10:28

Folks,

I've spent the afternoon reading over the forums as well as the many instructions that you have all posted. I've finally come up with something that gets close to what I need, but I'm only able to parse the information that I need into 2 useable columns, rather than the 3 that I need.

One major note, I am not able to declare cursor, drop tables, create views, or any of that other snazzy stuff that is demonstrated on this great site. We are using SQL Server 2005, however, the GUI that I have access to only allows SELECT statements. If I start with Declare@ or anything other than a SELECT statement, it will always throw an error saying, "please enter a select statement". This is a security measure from our IT department and I can not get around it. Please let me know if this data set is unclear or if you need anything further.

SAMPLE DATA:
RemarkID = 1
Remark = {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 System;}} \viewkind4\uc1\pard\b\f0\fs20 #Run 509 Route 6/16 .Takes PV 2682 @1142# \par }

{NOTE: the # before 'Run', the . before 'Takes' and the # after 1142 were all entered in the DB so that I could parse the data using the query. This is the closest I've come to getting this split.

This is the query:

select
r.remarkid, parsename(replace(r.remark, '#', '.'),3) as 'Run & Route',
parsename(replace(r.remark, '#', '.'),2) as 'PV/Instructions/Time',
parsename(replace(r.remark, '#', '.'),1) as 'Useless Field...Instructions should go here but this always comes out as \par }'
from Remarks R

{Note: As the 4th column name says, I'd like to put the Instructions in that column instead of in the 3rd column. However, no matter how I change the separators, the only thing that comes out in the 4th column is "\par }"

Thanks in advance!
Craig

EDIT: Sorry...I forgot to post the desired result...here it is:

RemarkID***Run & Route*** Instructions/PV***Time
1 ***Run 509 Route 6/16***Takes PV 2682***@1142

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-29 : 00:40:56

Try with dbo.fnParseString instead.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 00:45:31

Assuming your remark field data has a consistent format as below, use this
RemarkID = 1
Remark = {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 System;}} \viewkind4\uc1\pard\b\f0\fs20 #Run 509 Route 6/16 .Takes PV 2682 @1142# \par }


SELECT RemarkID,
REVERSE(SUBSTRING(REVERSE(Remark),CHARINDEX('#',REVERSE(Remark))+1,CHARINDEX('@',REVERSE(Remark))-CHARINDEX('#',REVERSE(Remark)))) AS Time,
REVERSE(SUBSTRING(REVERSE(Remark),CHARINDEX('@',REVERSE(Remark))+2,CHARINDEX('.',REVERSE(Remark))-CHARINDEX('@',REVERSE(Remark))-2)) AS [Instructions/PV],
REVERSE(SUBSTRING(REVERSE(Remark),CHARINDEX('.',REVERSE(Remark))+1,CHARINDEX('#',REVERSE(Remark),CHARINDEX('.',REVERSE(Remark)))-CHARINDEX('.',REVERSE(Remark))-1)) AS [Run & Route]
FROM YourTable

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-29 : 00:48:09

Hi Peso, Thanks for the reply...given the query I wrote, do you have any suggestions how I would input that instead of Parsename?

I read your post at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 but not quite sure how to apply that here.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 00:51:36

Did you try my suggestion? DId it work? Its untested, but should work fine as long as your string input has consistent format with those #'s and .'s
Anyways do let me know how you got on.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-29 : 01:06:24

Hi Vis...I wrote that before I saw your post. Yes...that worked perfectly, thanks! I'll be conscious to ensure consistency of the data input there. Do you have either an explanation of how that worked in layman's terms or a link where I could read up on it? I think I understand it, but it's a bit over my head and I would like to be able to use it on my own in the future.

Thanks so much!
Craig

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 01:13:58

quote:
Originally posted by flamblaster

Hi Vis...I wrote that before I saw your post. Yes...that worked perfectly, thanks! I'll be conscious to ensure consistency of the data input there. Do you have either an explanation of how that worked in layman's terms or a link where I could read up on it? I think I understand it, but it's a bit over my head and I would like to be able to use it on my own in the future.

Thanks so much!
Craig

The solution i provided just looks for positions of @,#,... using CHARINDEX function.
for example,consider value for Time field
the field value actually lies between last # and space before it. So what i've done is first reverse the string to start from backwards, looks for #, starts returning charcters from it until we reach next space. so returned value will be 2411@. now just reverse it once again to get your value @1142.
Similarly for next one start from @ to . and get string in between and reverse. and finally get bit between . and # after that and reverse it.

REVERSE() is used for reversing values
http://doc.ddart.net/mssql/sql70/ra-rz_15.htm

CHARINDEX() is used for getting position numbers of characters
http://doc.ddart.net/mssql/sql70/ca-co_5.htm

SUBSTRING() is to get string in between
http://doc.ddart.net/mssql/sql70/setu-sus_17.htm

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-11-29 : 01:36:21

Vis...that's extremely helpful! Thanks so much. I've been using substring for quite some time,but it's limiting in that you always have to know your start position number. With charindex, it looks like I'll be able to assign start/end numbers by using characters. Thanks again!

Craig

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 01:37:55

quote:
Originally posted by flamblaster

Vis...that's extremely helpful! Thanks so much. I've been using substring for quite some time,but it's limiting in that you always have to know your start position number. With charindex, it looks like I'll be able to assign start/end numbers by using characters. Thanks again!

Craig

You're welcome

Feel free to post whenever you've any doubt
Always happy to help you out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources