Removing white spaces in a varchar column

I have a table . It has a nullable column called AccountNumber, which
is of varchar type. The AccountNumber is alpha-numeric. I want to take
data from this table and process it for my application. Before doing
that I would like to filter out duplicate AccountNumbers. I get most of
the duplicates filtered out by using this query:

select * from customers
where AccountNumber NOT IN (select AccountNumber from customers where
AccountNumber <> '' group by AccountNumber having count(AccountNumber)
> 1)

But there are few duplicate entries where the actual AccountNumber is
same, but there is a trailing space in first one, and hence this
duplicate records are not getting filtered out. e.g
"abc123<white-space>" and "abc123" are considered two different entries
by above query.

I ran a query like :

update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)

But even after this query, the trailing space remains, and I am not
able to filter out those entries.

Am I missing anything here? Can somebody help me in making sure I
filter out all duplicate entries ?

Thanks,
RadAre you sure it's a whitespace? It might be a line break. To verify,
you might try something like:

SELECT AccountNumber
FROM customers
WHERE AccountNumber LIKE '%" + CHAR(10)
OR AccountNumber LIKE '%' + CHAR(13)

Oh, and always be cautious about running an UPDATE statement with no
WHERE clause.

HTH,
Stu|||Thanks Stu, you made my day. :)

It was a line break after a white space. I was concentrating only on
the white space and did not notice the line break. So in my first
statement, I first use this query:

UPDATE customers
SET AccountNumber = substring(AccountNumber, 1, PATINDEX('CHAR(13)',
AccountNumber))
WHERE AccountNumber like '%' + CHAR(13)

and it worked!

Thanks again!

Regards,
Rad

Stu wrote:
> Are you sure it's a whitespace? It might be a line break. To verify,
> you might try something like:
> SELECT AccountNumber
> FROM customers
> WHERE AccountNumber LIKE '%" + CHAR(10)
> OR AccountNumber LIKE '%' + CHAR(13)
> Oh, and always be cautious about running an UPDATE statement with no
> WHERE clause.
> HTH,
> Stu|||>> table . It has a nullable column called AccountNumber, which is of VARCHAR(n) type. <<

That is a very bad code design because it prevents check digits and
makes validation rules more complex. If you had validation rules in the
DDL, you would not have this problem. First mop the floor, then fix
the leak.

Removing the time from the results of GETDATE()

I have been using CONVERT(datetime, CONVERT(varchar,
GETDATE(), 1)) to remove the time portion of the date,
and it has worked reliably for a long time. Due to
changes in the server's environment (it has been moved to
Norway), this statement will no longer work when invoked
from within Access. Is there an alternative method for
doing this that does not risk complications due to
differing date formats?
Any help would be greatly appreciated - the errors this
is causing is wreaking havoc on an application that has,
up to now, run reliably for almost nine years and three
versions of SQL Server!
Daniel Inman
Intelligent Query EnginesThe best date format for international apps is yyyymmdd. Try:
SELECT CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112))
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Daniel Inman" <anonymous@.discussions.microsoft.com> wrote in message
news:069601c3aa51$9a6e5220$a301280a@.phx.gbl...
> I have been using CONVERT(datetime, CONVERT(varchar,
> GETDATE(), 1)) to remove the time portion of the date,
> and it has worked reliably for a long time. Due to
> changes in the server's environment (it has been moved to
> Norway), this statement will no longer work when invoked
> from within Access. Is there an alternative method for
> doing this that does not risk complications due to
> differing date formats?
>
> Any help would be greatly appreciated - the errors this
> is causing is wreaking havoc on an application that has,
> up to now, run reliably for almost nine years and three
> versions of SQL Server!
> Daniel Inman
> Intelligent Query Engines|||Some of the ways to get this are :
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112))
SELECT CAST(CONVERT(CHAR(8),CURRENT_TIMESTAMP,112) AS DATETIME)
SELECT CURRENT_TIMESTAMP - {fn CURRENT_TIME}
SELECT CONVERT(DATETIME, {fn CURRENT_DATE()})
SELECT {fn curdate()}
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Daniel Inman" <anonymous@.discussions.microsoft.com> wrote in message
news:069601c3aa51$9a6e5220$a301280a@.phx.gbl...
> I have been using CONVERT(datetime, CONVERT(varchar,
> GETDATE(), 1)) to remove the time portion of the date,
> and it has worked reliably for a long time. Due to
> changes in the server's environment (it has been moved to
> Norway), this statement will no longer work when invoked
> from within Access. Is there an alternative method for
> doing this that does not risk complications due to
> differing date formats?
>
> Any help would be greatly appreciated - the errors this
> is causing is wreaking havoc on an application that has,
> up to now, run reliably for almost nine years and three
> versions of SQL Server!
> Daniel Inman
> Intelligent Query Engines|||Hi,
If you want the date in a varchar, use:
select convert(varchar, Getdate(),1)
If you want it as a datetime:
select convert(datetime,convert(varchar, Getdate(),1))
(the time portion will be 00:00:00.000)
Rups|||> If you want it as a datetime:
> select convert(datetime,convert(varchar, Getdate(),1))
> (the time portion will be 00:00:00.000)
This is what Daniel was using already, and the problem is that we shouldn't
write code like this as it doesn't work in an international environment:
set language us_english
select convert(datetime,convert(varchar, Getdate(),1))
set language FRENCH
select convert(datetime,convert(varchar, Getdate(),1))
set language us_english
Code 112 should be used (as per the pther posts) as it prodices a "safe"
format: 'yyyymmdd'.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rupesh Kaslay" <rupeshkaslay@.yahoo.com> wrote in message
news:08eb01c3aa7a$28595120$a101280a@.phx.gbl...
> Hi,
> If you want the date in a varchar, use:
> select convert(varchar, Getdate(),1)
> If you want it as a datetime:
> select convert(datetime,convert(varchar, Getdate(),1))
> (the time portion will be 00:00:00.000)
> Rups
>

Removing spaces in Varchar 2000 field

I have a data field defined as Varchar 2000.
I need to remove all spaces greater than 2 in length from the inside of the data. I can remove beginning and ending spaces.

Data Example:
"AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

Need this:
"AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

Can anyone help?The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.|||Originally posted by mikeinoxford
The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.

Let say 1=space

select replace('qw11er11tt','11 ','1')|||I will test that. I'm currently working on a process using 'REPLACE'|||One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.|||I have the same problem|||And the key here is that you want to retain 1 space between each "word".

Yes?|||This probably isn't perfect, but it works. Note that the last (2) replace occurs twice, that is important.DECLARE @.c1 VARCHAR(20)
, @.c2 VARCHAR(20)
, @.c4 VARCHAR(20)
, @.c8 VARCHAR(20)
, @.c16 VARCHAR(20)
, @.cDemo VARCHAR(128)

SELECT @.c1 = Space(1)
, @.c2 = Space(2)
, @.c4 = Space(4)
, @.c8 = Space(8)
, @.c16 = Space(16)
, @.cDemo = 'This is a test of the ability to'
+ ' remove spaces.'

SELECT Replace(Replace(Replace(Replace(Replace(@.cDemo, @.c16, @.c1)
, @.c8, @.c1), @.c4, @.c1), @.c2, @.c1), @.c2, @.c1)-PatP|||I'm stuck...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy dog'
GO

SELECT REPLACE(REPLACE(Col1,SPACE(2),'@.'),'@. ',' ') AS Col1 FROM myTable99
GO|||And one more

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy' + SPACE (2145) + 'dog'
GO

DECLARE @.c1 VARCHAR(20)
, @.c2 VARCHAR(20)
, @.c4 VARCHAR(20)
, @.c8 VARCHAR(20)
, @.c16 VARCHAR(20)
, @.c32 VARCHAR(20)
, @.c64 VARCHAR(20)
, @.c128 VARCHAR(20)

SELECT @.c1 = Space(1)
, @.c2 = Space(2)
, @.c4 = Space(4)
, @.c8 = Space(8)
, @.c16 = Space(16)

SELECT Replace(Replace(Replace(Replace(Replace(Replace(Co l1, @.c16, @.c1)
, @.c8, @.c1), @.c4, @.c1), @.c2, @.c1), @.c2, @.c1), @.c2, @.c1) FROM myTable99
GO

There's gotta be a better way...|||There's gotta be a better way...I don't know of any better way using pure Transact-SQL. While it is ugly, this really is pretty fast and not obnoxiously difficult to code.

-PatP|||One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.

So if there are three consecutive spaces, do you want them ALL removed, or did you want to leave one space as a separator?|||...and is this a one type deal or part of a repeatable process? Are you looking at actually modifying the underlying data, or just removing the spaces for display. 'Cause its not clear here even what type of code is appropriate; View, Stored Procedure, User-defined function?

Here is one method that cleans all but one intervening space from the data in the table:

SET NOCOUNT ON
CREATE TABLE #myTable99(Col1 varchar(8000))
GO

INSERT INTO #myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog'
UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy' + SPACE (2145) + 'dog'

update #myTable99
set Col1 = replace(Col1, ' ', ' ')
where charindex(' ', Col1) > 0

while @.@.RowCount > 0
update #myTable99
set Col1 = replace(Col1, ' ', ' ')
where charindex(' ', Col1) > 0

select * from #myTable99

drop table #myTable99|||I steel have problem

I have table with nvarchar and numbers in it.
It's imposible to convert it to int so I was thinking about spaces behand the number bat the resoliution you showed dosn't works in this case.

So I'm thinking about function (maybe something els) which will take only my numbers which i can convert to int.

Do you have any idea|||I import the table from XLS. and in XLS it looks like space behind the number.|||Not the same problem. Try using RTRIM to get rid of trailing blanks and/or LTRIM to get rid of leading ones.|||That's a different problem than the one that I tried to solve earlier, even though the first problem was amusing!

I'd use something like the following, after you modify it to suit your schema and data:SELECT *
FROM myTable
WHERE 0 = IsNumeric(myColumn)-PatP|||Anyone want to post some sample DDL and DML and expected results like we did...

My telepathic infrared port is not working right now...

My Guess...you got one column that is space delimited with numbers..yes?

You need a UDF to parse it out then strip the numbers and place them in their own columns in another table...|||EDIT: And you should really start a new thread instead of hichjacking this one...|||Absolutely Rafala. Do NOT tag your issue onto the end of this thread. It is WAY too confusing trying to keep track of which problem to solve.|||Would the looping on this be too much?

declare @.str varchar(100)

set @.str = 'this is a test of some code'

select @.str

while charindex (' ', @.str) > 0
begin
select @.str = replace (@.str, ' ', ' ')
end

select @.str

Can't really apply that to a column, though...|||Don't know what this is like perormance wise (performance hit of recusive function calling and both LEN function calls)...but it will stop once it finds no more spaces to replace

CREATE FUNCTION dbo.RecursiveReplace(@.TheString VARCHAR(2000)) RETURNS VARCHAR(2000) AS
BEGIN
DECLARE @.Result VARCHAR(2000);

SET @.Result = REPLACE(@.TheString, ' ', ' ');

IF LEN(@.Result) <> LEN(@.TheString)
SET @.Result = dbo.RecursiveReplace(@.Result);

RETURN @.Result;
END|||I have figured a problem with REPLACE function.

"the space" I have copied from XLS file.|||I mean solved the problem.

Removing character from varchar

Hello all,
How would you remove the last character from the following string:
jmy0084t?
Thanks
JonJon wrote:
> Hello all,
> How would you remove the last character from the following string:
> jmy0084t?
> Thanks
> Jon
>
select left('jmy0084t',len('jmy0084t')-1)|||Hi
Such as
DECLARE @.str varchar(10)
SET @.str = 'jmy0084t?'
SELECT @.str,LEFT(@.str,LEN(@.str)-1),STUFF(@.str,LEN(@.str),1,'')
John
"Jon" wrote:
> Hello all,
> How would you remove the last character from the following string:
> jmy0084t?
> Thanks
> Jon
>