Is there another way for this??

Question

Is there another way for this??

dB-226266

Ten Centuries

Points: 1298
More actions
May 8, 2005 at 9:07 pm

#66268

DECLARE @unicodestr nvarchar(100)
SET @unicodestr = 'mystring '
while EXISTS(Select 1 where @unicodestr like '% ')
begin

SELECT @unicodestr = LEFT(@unicodestr, Len(@unicodestr) - 1)
if Len(@unicodestr) = 0
break
end
SELECT 'Pre' + LTRIM(@unicodestr) + 'Suff' AS 'Trimmed String'

Basically what this does is trim off the widecharacter space in the right side of the string. I cannot use RTRIM for this because I tried it and it didn't work.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Rob Farley SSCrazy Points: 2739 More actions · Answer 1

So... you're wanting an rtrim function?

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

dB-226266 Ten Centuries Points: 1298 More actions · Answer 2

dB-226266

Ten Centuries

Points: 1298

May 8, 2005 at 9:30 pm

#557519

yup.

Rob Farley SSCrazy Points: 2739 More actions · Answer 3

Have you seen: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_6xm5.asp?frame=true ?

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

dB-226266 Ten Centuries Points: 1298 More actions · Answer 4

yup on the other one too... hehehe ..

the space I'm trimming off is not an ordinary space... it's double width... something.. unicode. something you know... hehehe.. I don't know if the term is right... basically not the same as the normal width space.

Rob Farley SSCrazy Points: 2739 More actions · Answer 5

Ok. So maybe pump the string through a replace() function first, so that it is a normal space?

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

I believe that it's called an "M-Space" and occupies the same width as the letter 'M", hence the name. Since it's not a regular space character (ASCII 32), you may have to figure out what the ASCII code is and then try the following...

SELECT 'Pre' + REPLACE(@unicodestr,CHAR(x),'') + 'Suff' AS 'Trimmed String'

...where "x" is the ASCII character code for the M-Space. Keep in mind that the above which remove ALL M-Spaces in the string, not just the one on the end.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob Farley SSCrazy Points: 2739 More actions · Answer 7

Yup. Which is why you might want to push it through the replace function and then use rtrim, and maybe after that even push it back through a replace function, if you want to keep the 'M-space's.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

dB-226266 Ten Centuries Points: 1298 More actions · Answer 8

I only know how to make one... it's a japanese character for space... and I only need to trim the ones to the right.

dB-226266 Ten Centuries Points: 1298 More actions · Answer 9

Hmmm... Well I finally got your message there... but how about the original normal width space... what the original code actually does is take off both of the normal and M spaces...

Rob Farley SSCrazy Points: 2739 More actions · Answer 10

Ugh. Yeah, ok... If you have a combination of normal spaces and M spaces throughout the string that you need to preserve, then you will have to play around a little more. You could easily create a function that did it... using something like:while(right(@str,1) in (' ',N' '))begin set @STR = substring(@str,len(@str)-1)endreturn(@str)

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

dB-226266 Ten Centuries Points: 1298 More actions · Answer 11

dB-226266

Ten Centuries

Points: 1298

May 8, 2005 at 11:22 pm

#557534

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 12

May be of some use:-

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarsqlsg/html/msdn_interntl.asp

Steve

We need men who can dream of things that never were.

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 13

Hi I am a little confused you said that the string will contain both regular(ASCII) spaces and UNICODE double width spaces? If the String is stored in UNICode then all the spaces will be UNICODE or did I mis-read something. All UNICODE characters require 2-bytes.

And yet another page from MS. You have to love reading their documentation. This is from MSDN at http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/sql_dataencoding.asp

In addition to the UNICODE() and NCHAR() functions, the following string manipulation functions support Unicode wherever possible: CHARINDEX(), LEFT(), LEN(), UPPER(), LOWER(), LTRIM(), RTRIM(), PATINDEX(), REPLACE(), QUOTENAME(), REPLICATE(), REVERSE(), STUFF(), SUBSTRING(), UNICODE(). These functions accept Unicode arguments, respect the two-byte character boundaries of Unicode strings, and use Unicode sorting rules for string comparisons when the input parameters are Unicode.

HTH Mike

bersileus sacamay-126226 SSChasing Mays Points: 642 More actions · Answer 14

DECLARE @unicodestr nvarchar(100)

SET @unicodestr = 'mystring '

--while EXISTS(Select 1 where @unicodestr like '% ')

--begin

--SELECT @unicodestr = LEFT(@unicodestr, Len(@unicodestr) - 1)

-- if Len(@unicodestr) = 0

-- break

--end

SELECT 'Pre' + RTRIM(@unicodestr) + 'Suff' AS 'Trimmed String'

--result of using RTRIM

--PremystringSuff

is this the result you wanted or something else? i would agree with mike that i too am confused about your question?