Removing dashes to the right - SQL Server Forums

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

Transact-SQL (2000)

Removing dashes to the right

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-09-11 : 11:39:21

Hey Guys -
Here's my scenario:
I have a table with Error Codes, 1 record can have up to 10 error codes (ErrorCode1 - ErrorCode10), the client asked me to add all these codes togheter on the a single column for a report request and I decided to use " - " as the separator(ErrorCode1 + '-' + ErrorCode2...).
Most of the times, accounts have 3 error codes the most, so I end up having a result that looks like this:

Error1 - Error2 - Error3------

So, after adding these fields togheter, how can I remove any hyphens on the right of my string ?

Thanks in advance for the help.

---
"There's no sexy way to carry a lunch bag." My boss

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 11:51:09

I reckon you've got two choices:

1) REVERSE the string, find the position of the first non-hyphen character, and then chop that many characters off the end.

2) Use one of the methods for replacing multiple-strings - basically you would replace "--" with "-X", "-X-" with "-X" and then replace all "-X" with nothing. "X" needs to be a character which cannot occur within the string itself, of course!

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-11 : 11:52:01

Here is a possible implementation of Kristen's first suggestion:


select left(error, len(error) - patindex('%[^-]%', reverse(error)))

from	(
	select error = 'Error1 - Error2 - Error3------' union
	select 'Error1 - Error2 --' union
	select 'Error1'
	) errors

output:
------------------------------ 
Error
Error1 - Error2 - Error
Error1 - Error2

Be One with the Optimizer
TG

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-11 : 12:20:31

Another option is to search for occurrence of '--' and find the part just before that, like below:

select case when charindex('--',error) > 0 then substring(error,1,charindex('--',error)-1) else error end

from	(
	select error = 'Error1 - Error2 - Error3------' union
	select 'Error1 - Error2 --' union
	select 'Error1'
	) errors

Harsh Athalye
India.
"Nothing is Impossible"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-11 : 12:21:23

How are you creating the error string in the first place? It sounds like it may be better to put some conditional logic in there (perhaps with a CASE) so that you don't add in all the extra dashes in the first place?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-09-11 : 17:02:59

Absolutely. What is the code you are using to concatenate the strings?

"I have HAD it with these muthu-f$#%in' cursors in my muthu-f$#%in' database!"

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-09-11 : 17:39:31

Use a prefixing logic rather than suffixing logic while building the string.

Concatenate the adjacent strings with "-" prefixed only if the string is valid.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-11 : 18:13:48

This should work too:

SELECT Replace(Rtrim(Replace(error, '-', ' ')), '   ', ' - ') FROM myTable

It replaces all "-" with a space, trims the rightmost spaces, then replaces 3 spaces with " - " to match the original formatting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources