Better way to do this, or just find a better RTRIM

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

SQL Server 2000 Forums

SQL Server Development (2000)

Better way to do this, or just find a better RTRIM

Author

Topic

Lavos
Posting Yak Master

200 Posts

Posted - 2002-03-07 : 20:29:36

Okay, first some history, and then a section at the bottom about writing a better trimming function. I wish there was a better way to go about this, which is why I'm sharing the history, but I think I already have the solution. I'm just curious about other opinions.

HISTORY

I've unfortunately got to write some nice reports into another database that I'm accessing through OPENQUERY from sql server. Now, since I have absolutely no control over the other database I've got to deal with some fugly designs.

In this particular case, there is a big comment's table. (We'll call it cmt_det) This table has a cmt_indx (an int datatype) and a cmt_seq (also an int) as it's primary key. More or less, anything in the database can store comments about itself in this table, and link to it with a whatever_cmt_indx column. The cmt_seq more or less provides additional "pages" of comments as far as I can tell.

Now here's where it gets really fun.

Each row has a character column, that's also an array. Sql Server sees it like this
CREATE TABLE cmt_det (cmt_indx int, cmt_seq int,
cmt_text{001} varchar(73), cmt_text{002}, cmt_text{003}.....
..cmt_text{015} )
-- And a few other fields that don't matter. I'm leaving out the nullability to.

I'd like to grab all of these fields (and there can be multiple pages thanks to cmt_seq) and stuff them into a varchar variable with a carriage return between each line. I've got that working.

Unfortunately, not all of the comments have to be used before going on to the next page. Which means that the first 4 fields might be used on one, and then it skips to the next sequence number/page. I did have something that wouldn't stuff the blank lines into the string I was building, but that got thrown out since it's possible to have blank lines (for formatting) between two entries. Here's an example of what
a page might look like.

cmt_seq 1: -- I wouldn't have this line, just illustrating where the pages are. Also, the lines below are numbered for illustration purposes only.

1: First line
2:
3: Third line, with 2nd line blank.
4:
5:
.....
15:
cmt_seq 2:
1: First line of second "page"
2: Some more text
3: The last line of usable text
4:
.....
15:

The resulting string should end up looking like this. (I don't need to maintain "pages" for what I'm using it for):
'First line

Third line, with 2nd line blank.
First line of second "page"
Some more text
The last line of usable text'

Now onto a better trim function:

Now, if the RTRIM function worked like I wanted it to, this would work:
-- Yes, I'm ignoring the OPENQUERY stuff, this is for illustrative
-- purposes. I should also have null protection, and I'm renaming
-- the columns from the above example.
SELECT @StringVar=@StringVar + RTRIM(cmt1 + CHAR(13) + cmt2 +
char(13) + ..... cmt14 + CHAR(13) + cmt15)
FROM cmt_det
WHERE cmt_indx = @MyIndx
ORDER BY cmt_seq

Unfortunately, RTRIM only nails spaces. Much to my chagrin. I think I'm going to have to write a better trim function that removes all whitespace from the edges. (I'd probally do it by by writing seperate right and left functions.) Right now, I'm thinking of using a sequence table that just has numbers 1-8000, and querying against it using substring (or whatever) and finding the largest/smallest number that doesn't correspond to a whitespace character. I can then return LEFT/RIGHT using that number to trim off the whitespace.

Is there a better/more efficient way of doing this that I haven't thought of? I really don't want to write a crazy select statement with a 10 page case statement for my original query, but I'm not sure I want to write another whitespace trimming function either. (I looked, but couldn't find anything in the archives, but I don't think I was specific enough to find anything interesting.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 03/07/2002 21:15:24

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-03-07 : 22:29:00

Don't know if this will be any use to you..

Arnold wrote a function for me to remove unwanted characters. You may be able to adapt it for what you want.

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12238[/url]

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-03-08 : 16:14:51

Lavos,

The following looks for the last character that is a-z, A-Z, or 0-9. It then takes everything to the left of it. The output is "*&(*&1234567".

-----------------------------------------------------
declare @text varchar(100)
select @text='*&(*&1234567&^%$#'
select righttrimmed=left(@text,len(@text) + 1 - patIndex('%[a-z,A-Z,0-9]%',reverse(@text)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources