November 8, 2005 at 12:43 pm
I did an import from Excel to a table then in the column there's a space on the first letter.
e.g. Name
Andy
I check: SELECT charindex(' ', Firstname) FROM dbo.Sellers
Result: 6
Anyone had experience same problem? Any ideas how to fix it?
November 8, 2005 at 12:59 pm
G'Day,
I would try something like this:
UPDATE Sellers SET FirstName = LTRIM(RTRIM(FirstName))
I do this routinely when pulling data in from an external source.
Hope this helps,
Wayne
November 8, 2005 at 1:02 pm
I also tried LTRIM & RTRIM, still NO Luck!
November 8, 2005 at 1:11 pm
update seller
set firstname = replace(firstname,' ', '')
where charindex(' ',firstname) = 1
November 8, 2005 at 1:44 pm
hmmm...
Sounds like the first few characters of FirstName may not be space characters. You may be dealing with control characters. Here is a snippet from some of my data validation code. You may want/need to expand the range for ControlCharStringHigh.
DECLARE @ControlCharStringHigh varchar(100),
@ControlCharStringLow varchar(100)
SET @ControlCharStringLow = '[' + CHAR(1) + '-' + CHAR(30) + ']'
PRINT 'DEBUG: @ControlCharStringLow = ' + @ControlCharStringLow + CHAR(13)
SET @ControlCharStringHigh = '[' + CHAR(145) + '-' + CHAR(148) + ']'
PRINT 'DEBUG: @ControlCharStringHigh = ' + @ControlCharStringHigh + CHAR(13)
SELECT *
FROM YourTableName
WHERE FirstName LIKE @ControlCharStringLow
OR FirstName LIKE @ControlCharStringHigh
Wayne
November 8, 2005 at 1:51 pm
declare @Andy nvarchar(50)
select @Andy = 'andy' + char(13) + ' '
SELECT charindex(' ', @Andy)
result: 6
Gives an idea?
_____________
Code for TallyGenerator
November 9, 2005 at 2:11 am
Having about the same problem this might help:
select substring(Firstname,2,len(Firstname)) from dbo.sellers
The first character can be anything.
November 9, 2005 at 9:06 am
Here's a UDF you could use to examine each Firstname, or combine it with Wayne's query to view only those Firstnames that contain control characters.
The input string is displayed as a series of ASCII codes separated by a dash.
Usage:
-- SELECT dbo.fStrAscii('Hello')
-- SELECT dbo.fStrAscii(Firstname) FROM dbo.Sellers
--DROP FUNCTION dbo.fStrAscii
GO
CREATE FUNCTION dbo.fStrAscii
(
@s-2 varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
-- Returns the string @s-2 as a string of decimal ascii values
DECLARE @AscStr varchar(8000)
, @Len smallint
, @Pos smallint
SET @AscStr = ''
SET @Len = DataLength(@s)
SET @Pos = 0
WHILE @Pos < @Len
BEGIN
SET @Pos = @Pos + 1
SET @AscStr = @AscStr + CONVERT(varchar(3), ASCII(SUBSTRING(@s,@pos,1))) + '-'
END --WHILE
IF Len(@AscStr) > 0 AND Right(@AscStr,1) = '-'
SET @AscStr = Left(@AscStr, Len(@AscStr)-1)
RETURN @AscStr
END --FUNCTION
November 9, 2005 at 12:43 pm
What datatype is FirstName column? Is it CHAR?
-SQLBill
November 9, 2005 at 1:20 pm
Varchar (35)
November 9, 2005 at 1:39 pm
Am I the only one who cannot get Wayne's snippet to work. I created a little test table, inserted four entries. On with a leading char(13) and one ending with char(13) and the code did not find either.
CREATE TABLE dbo.testtable
(lngID INTEGER NOT NULL IDENTITY(1,2) PRIMARY KEY
,strDesc VARCHAR(50) NOT NULL
)
insert into testtable values ('One')
insert into testtable values ('Two')
insert into testtable values ('Three')
insert into testtable values ('Four' + char(13))
insert into testtable values (char(13) + 'Five')
Wayne's code snippet:
DECLARE @ControlCharStringHigh varchar(100),
@ControlCharStringLow varchar(100)
SET @ControlCharStringLow = '[' + CHAR(1) + '-' + CHAR(30) + ']'
PRINT 'DEBUG: @ControlCharStringLow = ' + @ControlCharStringLow + CHAR(13)
SET @ControlCharStringHigh = '[' + CHAR(145) + '-' + CHAR(148) + ']'
PRINT 'DEBUG: @ControlCharStringHigh = ' + @ControlCharStringHigh + CHAR(13)
SELECT *
FROM TestTable
WHERE strDesc LIKE @ControlCharStringLow
OR strDesc LIKE @ControlCharStringHigh
Howard
November 9, 2005 at 1:45 pm
noelson, did you read my post?
It's not 1st charachter, it's CR after the name!
_____________
Code for TallyGenerator
November 9, 2005 at 6:24 pm
This may help...for BOL:
SET TEXTSIZE 0
-- Create variables for the character string and for the current
-- position in the string.
DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
then check teh ASII number that is in the position your interested in. Then replace that value using replace(OriginalText, char(X), '') where X is the ASCII number.
Signature is NULL
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply