Removing Commas from column - now it's a mile long

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Removing Commas from column - now it's a mile long

Author

Topic

Johnnymarvellous
Starting Member

10 Posts

Posted - 2007-06-24 : 09:36:38

Hello,

I recently used the REPLACE command, as described in a previous topic on this forum, to remove unwanted commas however I've now got a new problem, the column has become half a mile long. I was asked to raise a new topic and give examples, see below:

CAN ANYONE TELL ME:
1. Why is the column now bigger?
2. How can I redue the size of the column to it's origional size?

I have already attempted to use CONVERT, RTRIM and CAST around the replace command, all give an error.

Example query and result before REPLACE:
select ICMAFinInstName,CptyCode from tradedetails

ICMAFinInstName CptyCode
---------------------------------------------------------------------- ----------------

Example query and result using REPLACE:

select replace (ICMAFinInstName,',',' ')AS NoCommaInst,CptyCode from tradedetails

NoCommaInstrument CptyCode
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------

(I see in the preview that this forum has removed the spaces between the titles, but the dashes (--) show the size of the columns

Thanks

JM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-24 : 09:41:17

ok i have no idea what you're talking about?
care to explain a bit more?
what do you mean by a mile long?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-24 : 11:51:19

My initial guess is:

1. The data type for ICMAFinInstName column is CHAR/NCHAR (not varchar)
2. In the REPLACE function, you are using SPACE character to replace COMMA character. You have to change your REPLACE() to replace comma with nothing i.e. just a pair of single quotes (no space in between)

Like this:

Select replace (ICMAFinInstName,',','')AS NoCommaInst,CptyCode from tradedetails

and not like this:

Select replace (ICMAFinInstName,',',' ')AS NoCommaInst,CptyCode from tradedetails

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Johnnymarvellous
Starting Member

10 Posts

Posted - 2007-06-25 : 05:32:44

Harsh - sorry, this didn't work. For the most part, there is only ever 1 comma within the column.
Changing the REPLACE command as you described, still leaves this column very long.

Is there a way I can change the length of the column?

Johnnymarvellous
Starting Member

10 Posts

Posted - 2007-06-25 : 05:35:05

quote:
Originally posted by spirit1

ok i have no idea what you're talking about?
care to explain a bit more?
what do you mean by a mile long?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Spirit 1, you must have read the title and not a lot else, see the example, notice how much longer the colum is after I use the replace command?

Ta

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-25 : 05:46:21

Firstly, tell us what is the data type of the column? what is the defined size?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 06:36:47

Set the result mode to Text in QA and run

declare @s varchar(10)
set @s='test,test'
select @s as test
select replace(@s,',','') as test

Now the lenght of second '-'s is 8000 which is maximum for varchar datatype

The similar case applies to this as well

declare @s datetime
set @s=getdate()
select cast(@s as varchar(30)),replace(cast(@s as varchar(30)),':','') as test

First length is 30 and second's is 8000

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 10:24:36

What's the issue ? This is not SQL issue. It is a presentation issue. That is just how QA present the result. The length of the result of the replace() command is non-deterministic so QA decide to show the max.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 10:28:31

quote:
Originally posted by khtan

What's the issue ? This is not SQL issue. It is a presentation issue. That is just how QA present the result. The length of the result of the replace() command is non-deterministic so QA decide to show the max.

KH
[spoiler]Time is always against us[/spoiler]

Yes it is so as the case for Getdate()

Select Getdate()

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 10:43:08

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

What's the issue ? This is not SQL issue. It is a presentation issue. That is just how QA present the result. The length of the result of the replace() command is non-deterministic so QA decide to show the max.

KH
[spoiler]Time is always against us[/spoiler]

Yes it is so as the case for Getdate()

Select Getdate()

Madhivanan

Failing to plan is Planning to fail

Yes. Saw your post on this over SSP.

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-25 : 10:48:34

OMG, this was all regarding just how QA displays results in text mode.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-25 : 10:55:56

and then i'm a bad reader... jeez...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 11:01:36

quote:
Originally posted by spirit1

and then i'm a bad reader... jeez...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Not your fault. The OP did not state the problem clearly. It is not a mile long. It is only 8000 chars long

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 11:08:03

Hey! I am using font-size 30000. It really is a mile long for 8000 characters...

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-06-25 : 11:44:57

You exaggerate - try

30000 points * 8000 in meters =

in Google

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 12:04:48

Cool!
But that was PS points (Standard 72 ppi, I believe).
I tried on my laser that has 3600 dpi...

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 01:10:49

quote:
Originally posted by Peso

Hey! I am using font-size 30000. It really is a mile long for 8000 characters...

Peter Larsson
Helsingborg, Sweden

How long did you take to read all characters?

Madhivanan

Failing to plan is Planning to fail

Johnnymarvellous
Starting Member

10 Posts

Posted - 2007-06-29 : 08:05:07

quote:
Originally posted by harsh_athalye

OMG, this was all regarding just how QA displays results in text mode.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

NO, it;s not just how QA dispalays it, it's alos how it's exported to file and is affecting the downstream system
double

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 08:08:14

quote:
Originally posted by Johnnymarvellous

quote:
Originally posted by harsh_athalye

OMG, this was all regarding just how QA displays results in text mode.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

NO, it;s not just how QA dispalays it, it's alos how it's exported to file and is affecting the downstream system
double

then just do a convert(varchar(100), <...>) to limit the size !

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 08:09:46

If you want fixed length, again cast it

declare @s varchar(10)
set @s='test,test'
select @s as test
select cast(replace(@s,',','') as varchar(10)) as test

Madhivanan

Failing to plan is Planning to fail

Johnnymarvellous
Starting Member

10 Posts

Posted - 2007-06-29 : 08:29:07

quote:
Originally posted by harsh_athalye

Firstly, tell us what is the data type of the column? what is the defined size?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

type:VARCHAR
lenght: 70
Fixed LenNullnSource: YES

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources