Is there another way for this??

Question

Is there another way for this??

Viewing 3 posts - 16 through 17 (of 17 total)

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

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

Edited to change char(10) which is a line feed to char(32) which is a space. Sorry guys been awhile since I have used ASCII codes to format text. BTW char(10) shows up as a space in grid mode but if you use text mode in QA then it shows as a Line Feed.

I goofed

Mike

/*

There is always more than one way.

--bersileus's method

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

--to remove both leading and trailing spaces

--This is by far the more elegant method (Although I would have added some spaces which were not asked for in the original post&nbsp

Another method would be to use the len function with the substring funciton

*/

--len returns the number of character not the number of bytes

--excluding trailling spaces

--

--Adjusting the number of trailing spaces does not change the length of the string

--However the trailing spaces are included when displaying the informaton

--Adjusting the size of the col showes that trailing spaces are included in the string

--Leading space (1)space my string... len =10 and Left Trim my String... Len left 9

DECLARE @unicodestr nvarchar(100)

DECLARE @SameunicodestrAfterLeftTrim nvarchar(100)

DECLARE @length int

DECLARE @Length2 int

SET @unicodestr = N' mystring '

SET @SameunicodestrAfterLeftTrim =Ltrim(N' mystring ')

SET @length =len(@unicodestr)--=9 1 leading space

SET @Length2 = len(@SameunicodestrAfterLeftTrim)--= 8 LTrim has removed the leading space

/*

--Example of len function with leading spaces

SELECT @unicodestr AS "Leading Space",

@length AS "Len leading Space",

@SameunicodestrAfterLeftTrim AS "Left Trim",

@Length2 AS "Len Left Frim"

*/

-- You can use the len function with the substring function to return only the characters

--of a string including embedded spaces and leading spaces

--OR

--use LeftTrim to remove leading spaces then use substring and len to set or view

--the data without trailing spaces

/*

--Example of using len and substring no space added to output

SELECT substring(@unicodestr,1,@length) AS "Leading Space",

substring(@SameunicodestrAfterLeftTrim,1,@length2) AS "Left Trim"

*/

SELECT 'Pre' + char(32) + --char(32) adds a space to your output

substring(@sameunicodestrAfterLeftTrim,1,@Length2) +

Char(32) +

'Stuff'

--bersileus's method

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

--bersileus's method with spaces

--SELECT 'Pre'+char(32) + LTRIM(RTRIM(@unicodestr))+ char(32) + 'Suff' AS 'Bersileus Trimmed String'

--HTH Mike

/*

Looks like 3 ways up to now. Your way, my way and the right way.

*/

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

Blame the japanese for that.. they have this code fragment.

RTRIM( RTRIM( w.K_SYOHIN_NAME ), ' ' ) "SHOHINNAME",

this is an oracle code.. the oracle RTRIM can have a second parameter which is the string to trim.. and the inner RTIM does just that.. it trims off the unicode space, then the outer one trims off the normal space.

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

Oracle’s RTRIM is propriety and does not match the RTRIM used in T-SQL. If your data is being stored in Unicode then all characters are 2 bytes long. The Datalength() function will return the number of bytes used to store a string. The Len Function returns the number of Characters in the string. In T-SQL RTRIM should remove all trailing spaces. It is not possible to mix char and Unicode values in the same string.

/***************************************************************************************

Check the string lengths using char and Nchar data types

****************************************************************************************/

DECLARE @UnicodeStr NChar(20)--fixed length

DECLARE @CharStr Char(20) --fixed length

DECLARE @VarUnicodeStr Nvarchar(20) --variable length string

DECLARE @VarCharStr VarChar(20) --variable length string

SET @UnicodeStr = N'Some String'

SET @CharStr = 'Some String'

SET @VarUnicodeStr = N'Some String'

SET @VarCharStr = 'Some String'

/*

With the fixed length strings UnicodeStr will use 40 bytes

and CharStr will use 20 Bytes

With Variable length strings Unicoed will use 22 bytes and

CharStr will use 11 bytes of memory

*/

--DataLength returns the number of bytes used

SELECT DataLength(@UnicodeStr)AS 'Unicode Fixed Length',

DataLength(@CharStr) AS 'Char Fixed Length',

DataLength(@VarUnicodeStr) AS 'Unicode variable Lenght String',

DataLength(@VarCharStr) AS 'Char Variable Length String'

/****************************************************************************************

All of the Unicode values require 2 X as many bytes as the same String using Char codes

******************************************************************************************/

/*

Use the Len function to determine the number of CHARACTERS in the string

The return value (11) will be the same for all data types used

*/

--Len returns the number of characters in a string excluding trailing spaces

SELECT Len(@UnicodeStr)AS 'Unicode Characters',

Len(@CharStr) AS 'Char Characters',

Len(@VarUnicodeStr) AS 'Unicode Characters',

Len(@VarCharStr) AS 'Char Characters'

--Trim the trailing spaces using RTRIM

--The Number of bytes used by Unicode is now 22 and Char is 11

--After RTRIM it does not matter if a string is fixed or variable lenght

--Unicode will require 22 bytes and Char will require 11 bytes

--DataLength returns the number of bytes used

SELECT DataLength(RTRIM(@UnicodeStr))AS 'Unicode Fixed Length',

DataLength(RTRIM(@CharStr)) AS 'Char Fixed Length',

DataLength(RTRIM(@VarUnicodeStr)) AS 'Unicode variable Lenght String',

DataLength(RTRIM(@VarCharStr)) AS 'Char Variable Length String'

/****************************************************************************************

All of the Unicode values require 2 X as many bytes as the same String using Char codes

******************************************************************************************/

--Len returns the number of characters in a string excluding trailing spaces

SELECT Len(RTRIM(@UnicodeStr))AS 'Unicode Characters',

Len(RTRIM(@CharStr)) AS 'Char Characters',

Len(RTRIM(@VarUnicodeStr)) AS 'Unicode Characters',

Len(RTRIM(@VarCharStr)) AS 'Char Characters'