April 15, 2003 at 7:29 am
for some reason the trailing spaces won't go when I use rtrim(field), any ideas as to why?
I'm trying to update a table with the results.
April 15, 2003 at 7:37 am
Are you using char or varchar?
SQL will pad a char field with spaces upto the max length.
Jeremy
April 16, 2003 at 6:30 am
it's nvarchar , which is why I don't understand it.
April 16, 2003 at 6:43 am
Could you provide more information on what you are doing?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 16, 2003 at 6:47 am
I have 2 tables with companyNames in, both should match but in some cases the 1 tables companyName has some trailing blanks and so an inner join fails. I tried to rtrim(companyName), but nothing happened.
In the end I updated them manually. Just bemused why it woul work:
update <tablename> set companyName = rtrim(companyName)
April 16, 2003 at 8:53 am
Strange, have you just for fun tried to put the field into a variable and then perform the rtrim operation on the variable?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 16, 2003 at 8:53 pm
quote:
it's nvarchar , which is why I don't understand it.
Those 'spaces' might not be actual space characters. They might be tabs, line feeds, carriage returns, etc.
April 16, 2003 at 9:40 pm
Add your own select for one record and execute the code. It will display asci values for none digit and none alphabetical characters.
Declare @Tmp Varchar(1000),
@i int
-- Fill in your own select
Select @Tmp=RTrim(LTrim(Column1)) From SomeTable
--
Set @i=1
While @i<=DataLength(@Tmp)
Begin
If Not ((SubString(@Tmp,@i,1)>='A' And SubString(@Tmp,@i,1)<='Z') Or
(SubString(@Tmp,@i,1)>='a' And SubString(@Tmp,@i,1)<='z') Or
(SubString(@Tmp,@i,1)>='0' And SubString(@Tmp,@i,1)<='9') Or
SubString(@Tmp,@i,1)=' ')
-- Select 'Position',@i,SubString(@Tmp,@i,1),' Ascii value=',Ascii(SubString(@Tmp,@i,1))
Select Ascii(SubString(@Tmp,@i,1))
Set @i=@i+1
End
You may see values of 9,13,10, etc. (tab,carriage returns,linefeeds)
April 17, 2003 at 2:49 am
Thanks very much for the interesting script finding non ascii chars, I modified it a little for easier viewing:
Set @i=1
While @i<=DataLength(@Tmp)
Begin
If Not ((SubString(@Tmp,@i,1)>='A' And SubString(@Tmp,@i,1)<='Z') Or
(SubString(@Tmp,@i,1)>='a' And SubString(@Tmp,@i,1)<='z') Or
(SubString(@Tmp,@i,1)>='0' And SubString(@Tmp,@i,1)<='9') Or
SubString(@Tmp,@i,1)=' ')
Select @i 'Position' , SubString(@Tmp,@i,1) 'char value',Ascii(SubString(@Tmp,@i,1)) 'Ascii value'
Set @i=@i+1
End
I might extend it further to flag where there is more than 1 space together, with the replace script, that would probably do the job, or even better, make sure the last char is not a space.
April 21, 2003 at 8:45 am
Here's another approach:
Set @i=1
While @i<=DataLength(@Tmp)
Begin
If Not (SubString(@Tmp,@i,1) LIKE '[A-Z]' Or
SubString(@Tmp,@i,1) LIKE '[a-z]' Or
SubString(@Tmp,@i,1) LIKE '[0-9]' Or
SubString(@Tmp,@i,1)=' ')
Select @i 'Position' , SubString(@Tmp,@i,1) 'char value',Ascii(SubString(@Tmp,@i,1)) 'Ascii value'
Set @i=@i+1
End
April 25, 2003 at 8:42 am
Don't know why I didn't think to shorten this up even more. Here ya go
SET NOCOUNT ON
DECLARE @i TINYINT, @Tmp VARCHAR(1000)
-- Fill in your own select
SELECT @Tmp='*83M,Nn@32xa;'
SET @i=1
WHILE @i <= DATALENGTH(@Tmp)
BEGIN
IF (SUBSTRING(@Tmp,@i,1) NOT LIKE '[A-Za-z0-9 ]')
SELECT @i 'Position' , SUBSTRING(@Tmp,@i,1) 'char value',ASCII(SUBSTRING(@Tmp,@i,1)) 'Ascii value'
SET @i=@i+1
END
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply