REPLACE Statement - Microsoft SQL Server

142 100+

SQL Query
SELECT mh.hpcode
, ISNULL(CONVERT(varchar,mh.opfromdt,112),'') as opfromdt
, ISNULL(CONVERT(varchar,mh.opthrudt,112),'') as opthrudt
, mc.patid
, mc.membid
, mc.lastnm
, mc.firstnm
, ISNULL(mc.mi,'') as 'mi'
, REPLACE(mc.street,char(10),'')as 'address'
, mc.city
, mc.state
, mc.zip
, mc.subssn as 'subscriberid'
, mc.ez_dbname as 'company'
, mh.currhist
, ISNULL(CONVERT(varchar,mc.birth,112),'') as 'birth'
FROM memb_hphists mh
LEFT JOIN rvs_memb_company mc
ON (mh.memb_keyid=mc.memb_keyid) and (mc.EZ_dbname=mh.EZ_dbname)
WHERE mh.EZ_history IS NULL
AND mh.EZ_dbname <> 'DCRFK'
AND mc.membid IS NOT NULL
AND mh.currhist = 'C'

Issue
I have a sql above that extract data to a text file from one of our database. The problem im having is that several records have either a line feed or a carriage return. Is there a way to do a nested REPLACE query to eliminate either line feed or carriage return.

Nov 15 '07 #1

Subscribe Post Reply

1598

iburyak

1,017

Expert 512MB

Try this:

Expand|Select|Wrap|Line Numbers

replace(replace(column_name, char(13), ''), char(10),'')

Good Luck.

Nov 15 '07 #2

Similar topics

SQL REPLACE STATEMENT - CONSOLIDATION

by: Craig Keightley | last post by:

Can these lines of sql statements be consolidated into one sql statement (possibly using reg exps??) BEGIN CODE ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Update...

PHP

syntax error in replace statement

by: middletree | last post by:

What's wrong with this code? strLongDesc = Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC rLf,"<br>"),"<",<),"<",>) Background: This field is a textarea, and I...

ASP / Active Server Pages

replace query string with post method

by: eddie wang | last post by:

I try to replace the following STATEMENT ONE with STATEMENT TWO. But it doesn't work. How to make it work??? Thanks. STATEMENT ONE: <td colspan="10" align="center"><a...

ASP / Active Server Pages

VB Replace function Error

by: Richard Gutery | last post by:

I have this piece of code in an ASP page: replace (strRootDir, "\", "/", -1) When I run the script I get: Microsoft VBScript compilation (0x800A0414) Cannot use parentheses when calling a Sub ...

ASP / Active Server Pages

CHARINDEX and STUFF/REPLACE

by: Little PussyCat | last post by:

Hello, I need to be able to replace only the first occurance of a space character in a column. Reason being is the data in the column I am trying to replace seems to have umpteen space...

Microsoft SQL Server

using a sql statement to do a string replace

by: bj daniels | last post by:

I am not certain which group to post this to - sorry if this is the wrong group: I have a table that holds the location of various media and such (mostly images). One field is the Media name,...

ASP.NET

replace function in C# part ii

by: Andy Sutorius | last post by:

Hi, I read the thread (2/16/05) regarding a replace function in C# however it didn't answer my question. I have a string which is building an insert sql statement and I would like to replace...

ASP.NET

stringbuilder replace doesn't work on vbLf , vbCrLf , vbCr

by: Guy | last post by:

Hi, I'm trying to run this code : strFileContentsHTML.Replace(vbLf, "<br>") strFileContentsHTML.Replace(vbCrLf, "<br>") strFileContentsHTML.Replace(vbCr, "<br>") It doesn't replace newline...

Visual Basic .NET

ORA-00904 error with create or replace view

by: Curtiosity | last post by:

I have done a create or replace view called creditcard1. If I do a "select * from creditcard1" it retrieves the data just fine. If I try to do a statement where I am listing the column names it...

General

regex replace problem.

by: Jeremy | last post by:

I am trying to replace a string "P" with "\P" as long as the string "P" does not already have a "\" in front. for my search string I've used @"(P)" so my regex replace statement is: ...

.NET Framework

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing