Replace carriage return and line feed in a SQL server varchar field

I have been facing an issue while importing some data from SQL to CSV file. In one of my SQL fields user entered Enter Key or Line feed, because of that my exported CSV file was not in good format. Rows were broken in between. The bwloe query replaces the Carriage return and line feed with 'a space:

SELECT REPLACE(REPLACE(Remarks, Char(10),' '), Char(13), ' ') as Remarks FROM DemoTable

Change the Default Value of a Column in already created Table

Use below method to alter or change the default value of a column of an existing table:


ALTER TABLE <Table_Name> DROP CONSTRAINT <Column_Constraint_Name>  
GO
ALTER TABLE <Table_Name> WITH NOCHECK   
ADD CONSTRAINT [<Column_Constraint_Name>] DEFAULT (-10) FOR <Column_Name>

Insert record if not exist

Sometime you may need to insert record in one table if that table does not contain the record. You can use IF to check if something exists (or not), and then act accordingly:


IF NOT EXISTS (SELECT * FROM CustomerMaster WHERE FName = 'Sholo' AND LName = 'Twango')

BEGIN

INSERT INTO 

CustomerMaster 

(FName, LName) 

VALUES 

(

'Sholo', 

'Twango'

) 

END

Querying special character like Percentage in Like operator

It is very tricky to search percentage character in some column. The below query will throw an error on execution:

SELECT LedgerName FROM LedgerMaster WHERE LedgerName LIKE '% %%' 

Instead of above query we had to use below query:


SELECT LedgerName FROM LedgerMaster WHERE LedgerName LIKE '% |%%' ESCAPE '|'

"ESCAPE" keyword are used within an Query to tell the SQL Server that the escaped part of the Query string should be handled differently. In above Query ESCAPE character is mentioned as '\' hence character after '|' is processed differently instead of normal there '%' character is search in "LedgerName" column of "LedgerMaster" table.