Replace with nothing in SQL Server

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

Replace with nothing in SQL Server

Author

Topic

dg19
Starting Member

15 Posts

Posted - 2010-11-09 : 07:37:59

Hi All,

I have a field where the records consist of multiple characters and then text. I would like to replace with nothing.

Could anyone hint how it can be done

Regards,

Amit

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 07:56:31

What is the datatype of that column?
Which characters are "normal" in that column?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 08:48:47

If it is a newline character try using replace(replace(col,char(13),''),char(10),'')

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 09:13:52

To find out what it is:

select 
ascii(substring(COLUMNNAME,patindex('%['+char(0)+'-'+char(31)+']%',COLUMNNAME),1)) as Ascii_Code,
* 
from YOURTABLE
where patindex('%['+char(0)+'-'+char(31)+']%',COLUMNNAME) > 0

No, you're never too old to Yak'n'Roll if you're too young to die.

dg19
Starting Member

15 Posts

Posted - 2010-11-12 : 07:23:07

quote:
Originally posted by webfred

What is the datatype of that column?
Which characters are "normal" in that column?

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi,
The column is varchar and contains alpha numeric values.

The character is a blank space inserted from a textfile.

Thanks & Regards,

Amit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources