Is there another way for this??
-
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.
-
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 -
yup.
-
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 -
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.
-
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 -
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)
Intro to Tally Tables and Functions -
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 -
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.
-
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...
-
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 -
-
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.
-
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
-
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?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply