How to remove box like characters

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to remove box like characters

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-03-04 : 08:30:38

Hello All,

I have a column in my table that contains whitespaces (box like characters) at the end of each field; does any body know how to solve this problem? That field should only allow 12 digits instead of 16(the field is a varchar datatype). An example is shown below for better clarification

Before the change:

7709787873??????
7725632053??????
5584????????????
0539????????????

After the change:

7709787873
7725632053
5584
0539

Any advice would be greatly appreciated. Thanks.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-04 : 08:40:47

Those are non printable characters IE Tab, CR, LF, etc, use "replace" and "char" to get rid of them.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 08:41:00

use LTRIM(RTRIM(Field))

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-04 : 08:56:26

[code]
declare @a table (
c1 varchar(20)
)

declare @x int
set @x = 2
while @x < 6
begin
insert into @a (c1) values ('TEST_'+char(@x))
set @x = @x + 1
end

--Rtrim(Ltrim( Does Not Work
select
c1,
len(c1) as LenOriginalColumn,
ltrim(rtrim(c1)) as Trimmed,
len(ltrim(rtrim(c1))) as LenTrimmedColumn
from @a

--Replacing the characters does.
select
c1,
len(c1) as LenOriginalColumn,
replace(replace(replace(replace(replace(c1, char(5), ''), char(4), ''), char(3), ''), char(3), ''), char(2), '') as replacedChars,
len(replace(replace(replace(replace(replace(c1, char(5), ''), char(4), ''), char(3), ''), char(3), ''), char(2), '')) as LenTrimmedColumn
from @a
[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

pras2007
Posting Yak Master

216 Posts

Posted - 2008-03-04 : 09:05:06

Thanks folks for the quick response. Jhocutt, I'm confused on which one of your code sample to use to solve my problem? Please provide the code sample to use if my field name is "col1". Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 04:00:49

select
col1,
len(col1) as LenOriginalColumn,
replace(replace(replace(replace(replace(col1, char(5), ''), char(4), ''), char(3), ''), char(3), ''), char(2), '') as replacedChars,
len(replace(replace(replace(replace(replace(col1, char(5), ''), char(4), ''), char(3), ''), char(3), ''), char(2), '')) as LenTrimmedColumn
from your_table

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 04:50:15

It seems that column only should have number characters.
Try this

select	yourcolumn,
	left(yourcolumn, case patindex('%[^0-9]%', yourcolumn) when 0 then yourcolumn else patindex('%[^0-9]%', yourcolumn) - 1 end)
From	yourtable

E 12°55'05.25"
N 56°04'39.16"

pras2007
Posting Yak Master

216 Posts

Posted - 2008-03-06 : 07:53:09

Peso, it seems that your script works but I get the following errors on some records. The error is shown below:

Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '7068251919' overflowed an int column. Maximum integer value exceeded.
The statement has been terminated.

Does anyone know how to correct this problem? Please advice. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-06 : 08:05:40

The value you are casting is out of range of integers. Please use a data type which can hold higher range of values( may be bigint)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:20:40

try

select yourcolumn,
left(yourcolumn, case patindex('%[^0-9]%', yourcolumn) when cast(0 as bigint) then yourcolumn else patindex('%[^0-9]%', yourcolumn) - 1 end)
From yourtable

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:27:12

and to extract only numbers http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

pras2007
Posting Yak Master

216 Posts

Posted - 2008-03-06 : 11:12:00

I'm still getting the same error message when I change it to bigint. I've changed the statement to an update statgement, does that matters? Please advice.

The SQL statement is provided below:

UPDATE mytable
SET mycolumn = LEFT(mycolumn, CASE patindex('%[^0-9]%', MYCOLUMN) WHEN cast(0 as bigint) THEN MYCOLUMN ELSE patindex('%[^0-9]%',
MYCOLUMN) - 1 END)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 15:46:31

Mycolomn is varchar, you wrote that in your first post.
So the error is NOT a result of this.

E 12°55'05.25"
N 56°04'39.16"

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-03-09 : 04:34:40

Pras2007,
Issuing the statement "Select char(5)" will yield the "white box" that you are trying to remove form your field.
Now to remove box "Select OriginalColName, Replace(fname, char(5), '') as CleanedColName from <tablename>". If you have other characters to remove, you can nest the replace function as demonstrated by madivanan or jhocutt or use peso's select statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources