CODEWITHSUNDEEP
sqlsql-server
user3514052
user3514052

Reputation: 429

How to trim characters from a string in Microsoft SQL Server?

I want to get record id by code in database. Problem is that many of codes in database are with line breaks or something else like: "12545 "

I tried this queries, but it did not work, I only get ids that code has no line breaks

SELECT * FROM Firms WHERE REPLACE(Code,'/n/r','') = '302489977'

SELECT * FROM Firms WHERE TRIM(Code) = '302489977'

Upvotes: 0

Views: 434

Answers (3)

Berty_Lurch
Berty_Lurch

Reputation: 86

It's difficult without seeing what your data structure is like, but something like this may work. CHAR13 and CHAR10 represent line breaks.

Specifically, CHAR(13) is carriage return and CHAR(10) is line feed.

Select Replace(Replace(@str,CHAR(10),''),CHAR(13),'')

Upvotes: 3

Dave
Dave

Reputation: 3288

REPLACE(Code,CHAR(13)+CHAR(10),'')

maybe

Upvotes: 1

txemsukr
txemsukr

Reputation: 1037

Maybe you want to use LIKE?

 SELECT * FROM Firms WHERE Code LIKE '%302489977%';

This will work if you can't have an id on multiple lines (that is to say on multiple lines) and if an other id can't be found in the code.

Upvotes: 0

Related Questions