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 spaces left by hidden columns or rows

I have written a comprehensive report that is used as a subreport for many
others. The comprehensive report supports dynamic grouping and the dynamic
hiding of columns or rows.
My problem is that if a column is hidden, a huge gap is seen in the report
where the column is hidden.
A C D
+=====+ (hidden column) +=====+==========+
|xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
...
Is there any way to make column A move to live where the hidden column "B"
would have been had it not been hidden?
Thanks.
--
Jim Campbell
FujiFilm eSystemsYou have to put the colum width to 0 (in the properties window)
"Jim Campbell" wrote:
> I have written a comprehensive report that is used as a subreport for many
> others. The comprehensive report supports dynamic grouping and the dynamic
> hiding of columns or rows.
> My problem is that if a column is hidden, a huge gap is seen in the report
> where the column is hidden.
> A C D
> +=====+ (hidden column) +=====+==========+
> |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> ...
> Is there any way to make column A move to live where the hidden column "B"
> would have been had it not been hidden?
> Thanks.
> --
> Jim Campbell
> FujiFilm eSystems|||If only it were that simple... I need to set the width based on whether or
not the column is hidden. Yet, I read the post below:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
that seems to imply there is no way to do this under the current release.
Has anyone found a work around?
- Jim
--
Jim Campbell
FujiFilm eSystems
"Antoon" wrote:
> You have to put the colum width to 0 (in the properties window)
> "Jim Campbell" wrote:
> > I have written a comprehensive report that is used as a subreport for many
> > others. The comprehensive report supports dynamic grouping and the dynamic
> > hiding of columns or rows.
> >
> > My problem is that if a column is hidden, a huge gap is seen in the report
> > where the column is hidden.
> > A C D
> > +=====+ (hidden column) +=====+==========+
> > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> >
> > ...
> >
> > Is there any way to make column A move to live where the hidden column "B"
> > would have been had it not been hidden?
> >
> > Thanks.
> > --
> > Jim Campbell
> > FujiFilm eSystems|||You probably have a condition that you use to hide the column; try setting
the colum width to =iif(condition;0;20)
"Jim Campbell" wrote:
> If only it were that simple... I need to set the width based on whether or
> not the column is hidden. Yet, I read the post below:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> that seems to imply there is no way to do this under the current release.
> Has anyone found a work around?
> - Jim
> --
> Jim Campbell
> FujiFilm eSystems
>
> "Antoon" wrote:
> > You have to put the colum width to 0 (in the properties window)
> >
> > "Jim Campbell" wrote:
> >
> > > I have written a comprehensive report that is used as a subreport for many
> > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > hiding of columns or rows.
> > >
> > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > where the column is hidden.
> > > A C D
> > > +=====+ (hidden column) +=====+==========+
> > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > >
> > > ...
> > >
> > > Is there any way to make column A move to live where the hidden column "B"
> > > would have been had it not been hidden?
> > >
> > > Thanks.
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems|||But I am pretty sure that u can't write an expression in the width property.
U can just enter numeric values..
am I wrong ?
"Antoon" wrote:
> You probably have a condition that you use to hide the column; try setting
> the colum width to =iif(condition;0;20)
> "Jim Campbell" wrote:
> > If only it were that simple... I need to set the width based on whether or
> > not the column is hidden. Yet, I read the post below:
> >
> > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> >
> > that seems to imply there is no way to do this under the current release.
> > Has anyone found a work around?
> >
> > - Jim
> > --
> > Jim Campbell
> > FujiFilm eSystems
> >
> >
> > "Antoon" wrote:
> >
> > > You have to put the colum width to 0 (in the properties window)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > I have written a comprehensive report that is used as a subreport for many
> > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > hiding of columns or rows.
> > > >
> > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > where the column is hidden.
> > > > A C D
> > > > +=====+ (hidden column) +=====+==========+
> > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > >
> > > > ...
> > > >
> > > > Is there any way to make column A move to live where the hidden column "B"
> > > > would have been had it not been hidden?
> > > >
> > > > Thanks.
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems|||Yeah you're rigth, sorry about that.
Now I rember how to do it. Put the width value to zero and flag the
properties/"can increase to accomadate length" .
When visible the colomn width will be fine, when invisible it will
be...truly invisible
"Jerome" wrote:
> But I am pretty sure that u can't write an expression in the width property.
> U can just enter numeric values..
> am I wrong ?
> "Antoon" wrote:
> > You probably have a condition that you use to hide the column; try setting
> > the colum width to =iif(condition;0;20)
> >
> > "Jim Campbell" wrote:
> >
> > > If only it were that simple... I need to set the width based on whether or
> > > not the column is hidden. Yet, I read the post below:
> > >
> > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > >
> > > that seems to imply there is no way to do this under the current release.
> > > Has anyone found a work around?
> > >
> > > - Jim
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > You have to put the colum width to 0 (in the properties window)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > hiding of columns or rows.
> > > > >
> > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > where the column is hidden.
> > > > > A C D
> > > > > +=====+ (hidden column) +=====+==========+
> > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > >
> > > > > ...
> > > > >
> > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > would have been had it not been hidden?
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems|||Hi,
Access to the Reporting Service site at Microsoft appears to be
hanging, so I'll reply using Google Groups...
Can you tell me where to find this 'can increase to accommodate length'
option? The only thing I see that is like this is "can increase to
accommodate height."
Can this width option be used if there is a textbox in the column? Can
a textbox widen to accommodate contents, too?
Thanks,
Jim Campbell
Antoon wrote:
> Yeah you're rigth, sorry about that.
> Now I rember how to do it. Put the width value to zero and flag the
> properties/"can increase to accomadate length" .
> When visible the colomn width will be fine, when invisible it will
> be...truly invisible
> "Jerome" wrote:
> > But I am pretty sure that u can't write an expression in the width property.
> > U can just enter numeric values..
> > am I wrong ?
> >
> > "Antoon" wrote:
> >
> > > You probably have a condition that you use to hide the column; try setting
> > > the colum width to =iif(condition;0;20)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > If only it were that simple... I need to set the width based on whether or
> > > > not the column is hidden. Yet, I read the post below:
> > > >
> > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > >
> > > > that seems to imply there is no way to do this under the current release.
> > > > Has anyone found a work around?
> > > >
> > > > - Jim
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > You have to put the colum width to 0 (in the properties window)
> > > > >
> > > > > "Jim Campbell" wrote:
> > > > >
> > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > hiding of columns or rows.
> > > > > >
> > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > where the column is hidden.
> > > > > > A C D
> > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > >
> > > > > > ...
> > > > > >
> > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > would have been had it not been hidden?
> > > > > >
> > > > > > Thanks.
> > > > > > --
> > > > > > Jim Campbell
> > > > > > FujiFilm eSystems|||I did my own research here, and I see that the "CanGrow" feature of
textboxes applies only to height and not width in this particular
version of MS Reporting Services. I do not see any way to do what I am
asking.
To illustrate what I have today:
col1--col2--col3--data1--data2
X Y Z
where col1 may or may not show field X if the user groups by field X,
col2 may or may not show field Y if the user groups by field Y, etc...
This means that if, for example, the user chooses not to group by field
Y, the report will appear like this
col1 col3--data1--data2
X Z
I suppose that if I found a way to have true dynmaic grouping, e.g., by
having col3 group by X, Y, or Z, rather than have each column represent
one field toggled on or off with grouping, I could it make it all
better, but I would still have the problem of spaces to the left of the
report, representing all the non-grouped columns.
I guess we'll have to put this problem down to the limitations inherent
in this "1.0-version" piece of software.
- Jim
jtk6204@.gmail.com wrote:
> Hi,
> Access to the Reporting Service site at Microsoft appears to be
> hanging, so I'll reply using Google Groups...
> Can you tell me where to find this 'can increase to accommodate length'
> option? The only thing I see that is like this is "can increase to
> accommodate height."
> Can this width option be used if there is a textbox in the column? Can
> a textbox widen to accommodate contents, too?
> Thanks,
> Jim Campbell
> Antoon wrote:
> > Yeah you're rigth, sorry about that.
> > Now I rember how to do it. Put the width value to zero and flag the
> > properties/"can increase to accomadate length" .
> > When visible the colomn width will be fine, when invisible it will
> > be...truly invisible
> >
> > "Jerome" wrote:
> >
> > > But I am pretty sure that u can't write an expression in the width property.
> > > U can just enter numeric values..
> > > am I wrong ?
> > >
> > > "Antoon" wrote:
> > >
> > > > You probably have a condition that you use to hide the column; try setting
> > > > the colum width to =iif(condition;0;20)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > If only it were that simple... I need to set the width based on whether or
> > > > > not the column is hidden. Yet, I read the post below:
> > > > >
> > > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > > >
> > > > > that seems to imply there is no way to do this under the current release.
> > > > > Has anyone found a work around?
> > > > >
> > > > > - Jim
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems
> > > > >
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > You have to put the colum width to 0 (in the properties window)
> > > > > >
> > > > > > "Jim Campbell" wrote:
> > > > > >
> > > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > > hiding of columns or rows.
> > > > > > >
> > > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > > where the column is hidden.
> > > > > > > A C D
> > > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > > >
> > > > > > > ...
> > > > > > >
> > > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > > would have been had it not been hidden?
> > > > > > >
> > > > > > > Thanks.
> > > > > > > --
> > > > > > > Jim Campbell
> > > > > > > FujiFilm eSystems

Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
Thanks
Nice thing, already discussed in here:
http://groups.google.de/groups?hl=de...gbl%26rnum%3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks

Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
ThanksNice thing, already discussed in here:
hx.gbl&rnum=1&prev=/ groups%3Fq%3Dblanks%26hl%3Dde%26lr%3D%26
group%3Dmicrosoft.publ
ic.sqlserver.programming%26selm%3DunisU2McEHA.3580%2540TK2MSFTNGP11.phx.gbl%26rnum
%3D1" target="_blank">http://groups.google.de/groups?hl=d...num
%3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks

Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
ThanksNice thing, already discussed in here:
http://groups.google.de/groups?hl=de&lr=&threadm=unisU2McEHA.3580%40TK2MSFTNGP11.phx.gbl&rnum=1&prev=/groups%3Fq%3Dblanks%26hl%3Dde%26lr%3D%26group%3Dmicrosoft.public.sqlserver.programming%26selm%3DunisU2McEHA.3580%2540TK2MSFTNGP11.phx.gbl%26rnum%3D1
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks

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 spaces from an nvarchar column

Hello,
This is a simple question, hopefully with a simple answer. I have
an nvarchar column of length 255. In one of the rows I have the
following sentance - 'See the brown ball bounce'. Is it possible to
use a command to remove all of the spaces in that sentance, so that
the sentance reads 'Seethebrownballbounce'? As you can see, I am not
just interested in getting rid of the trailing and leading spaces.

Thanks,
BillyYou can use REPLACE:

SELECT REPLACE(N'See the brown ball bounce', N' ', N'')

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Billy Cormic" <billy_cormic@.hotmail.com> wrote in message
news:dd2f7565.0411021606.9bbc6d5@.posting.google.co m...
> Hello,
> This is a simple question, hopefully with a simple answer. I have
> an nvarchar column of length 255. In one of the rows I have the
> following sentance - 'See the brown ball bounce'. Is it possible to
> use a command to remove all of the spaces in that sentance, so that
> the sentance reads 'Seethebrownballbounce'? As you can see, I am not
> just interested in getting rid of the trailing and leading spaces.
> Thanks,
> Billy

removing spaces from a text

Hi. In our database, we have a Social Security Number field. We've made application upgrades and we can no longer have the dashes ( - ) between the numbers. So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:

UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')

so, i tried this query and it does nothing.

UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')

does anybody have any ideas? Thanks!Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP|||...So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:
UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')The sample code you gave will do exactly what you wanted it to do, and would not have inserted spaces where the dashes were:select REPLACE('123-45-6789', '-','')So something else is going on. Either you have dirty characters in your data, or you ran a different statement than the one you posted.|||Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP
okay, i ran your code and i'm getting this in the results: 0x20
Is that a space?|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.
thanks for your post! your solution worked perfectly except i had to use this:

update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,8,4)
ELSE SSN
end

i had to insert an 8 b/c the last 4 digits of a ssn start at 8. but thanks for the help though! you solved my problem perfectly!

Removing spaces between words in sql

I guess there is no built in functions to do this but I have a function that replaces anything that is not A-Z with a space and returns @.data. What I additionally need the function to do is scrunch up @.data (remove all blanks betwwen each word so that 'I ran very fast' would be 'Iranveryfast').

What I need help in doing is the "Scrunch" part. Is there a way I could move the @.Data to something like @.DataHold and inspect each character, if it is not a blank, move that character back to @.Data?
This was pretty easy for me to do in C# with a while loop, but I do not know how to get it done in SQL Server 2005.

Thanks for any help!

Use the replace function.

replace( @.Data, ' ', '' )

For example,

DECLARE @.Data varchar(1000)
SET @.Data = 'I ran very fast'

SELECT replace( @.Data, ' ', '' )

Iranveryfast

Removing non-alpha characters & spaces script...

Hi I am trying to strip out any non-alpha characters from a field.

i.e. Field = ABC"_IT8*$ should return: ABCIT8

I am writing a loop to do this for all values of a field. The script runs, but hangs...please could somebody advise on the code below...:

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @.Index SMALLINT,
@.MATCH_Supplier_name varchar(500),
@.Counter numeric,
@.Max numeric
-- @.sqlstring varchar(500)

SET @.Counter = 1
SET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @.Counter <@.Max
BEGIN
SET @.MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @.Counter = DTect_Supplier_SRN)
SET @.Index = LEN(@.MATCH_Supplier_name)
WHILE @.Index > = 1
SET @.MATCH_Supplier_name = CASE
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[0-9]' THEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
ELSE ''
END + @.MATCH_Supplier_name
SET @.Index = @.Index - 1
--PRINT @.MATCH_Supplier_name
SET @.Counter = @.Counter + 1
ENDSET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

Change it to count ...don't have much time now,check for other errors.|||Cheers, its all sorted now.

removing blank spaces from output file

Is there a way to remove blank space padding from around
my result set sent to a text file using osql. I can only
seem to find a column_width setting. I am using the below
and am after a csv file without blank padding.
osql -S(local) -Uuser -Ppassword -s, -h-1 -w254 -
Q"DMSWEB.dbo.rf_test 200035" -oc:\TestReport3.txt
Thanks in advance.
Amelia
Hi,
Consider BCP with QUERYOUT option to do this.
Sample
bcp "exec DMSWEB.dbo.rf_test 200035" queryout
c:\TestReport3.txt -c -S(local) -Uuser -Ppassword
Thanks
Hari
MCDBA
"Amelai" <anonymous@.discussions.microsoft.com> wrote in message
news:2352201c45e43$427d3c60$a501280a@.phx.gbl...
> Is there a way to remove blank space padding from around
> my result set sent to a text file using osql. I can only
> seem to find a column_width setting. I am using the below
> and am after a csv file without blank padding.
> osql -S(local) -Uuser -Ppassword -s, -h-1 -w254 -
> Q"DMSWEB.dbo.rf_test 200035" -oc:\TestReport3.txt
> Thanks in advance.
> Amelia
|||Thanks heaps for your help Hari. Much appreciated.
:0)
Amelia

>--Original Message--
>Hi,
>Consider BCP with QUERYOUT option to do this.
>Sample
>bcp "exec DMSWEB.dbo.rf_test 200035" queryout
>c:\TestReport3.txt -c -S(local) -Uuser -Ppassword
>--
>Thanks
>Hari
>MCDBA
>"Amelai" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2352201c45e43$427d3c60$a501280a@.phx.gbl...
below
>
>.
>