How to Trim a Memo field?

Question

Post reply

How to Trim a Memo field?

Lim Chee Hwee-339629

SSC-Addicted

Points: 461
More actions
October 17, 2007 at 3:25 am

#62866

Hi everyone,
I need to trim a Memo field to do a string comparison. Basically, I m trying to match the following:
"ABC" and "ADGETEQASGEQWT ABC " Match
"ABC" and "ADGETEQASGEQWT ABC erq3252wr " Do not Match
The 2nd field is a Memo field.
I had an idea which is to reverse the string and do a patindex. However, I need to remove the trailing spaces or the value returned by patindex don't work.
Maybe someone that encounter this before can help me out?

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Use PATINDEX for searching patterns....

SELECT (CASE WHEN PATINDEX('%ABC%', YourMemoField) > 0 THEN 'Match' ELSE 'No Match' END )

FROM YourTable

--Ramesh

Lim Chee Hwee-339629 SSC-Addicted Points: 461 More actions · Answer 2

That won't work. My 2nd Example will still match. Anyway, Patindex don't accept Memo field. and doing '%ABC' will still consider as a match for 2nd Example also.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Am I right in inferring that there should only be a match if the "ABC" is at the end? How about this?

CREATE TABLE #Memo (ID int, Memo1 varchar(10), Memo2 varchar(30))

INSERT INTO #Memo

SELECT 1, 'ABC', 'ADGETEQASGEQWT ABC ' UNION

SELECT 2, 'ABC', 'ADGETEQASGEQWT ABC erq3252wr ' UNION

SELECT 3, 'John', 'Hello John' UNION

SELECT 4, 'John', 'Pretty Polly'

SELECT ID,

CASE IF RTRIM(Memo1) = RIGHT(RTRIM(Memo2), LEN(Memo1))

THEN 'Match'

ELSE 'No Match'

END

FROM #Memo

John

Lim Chee Hwee-339629 SSC-Addicted Points: 461 More actions · Answer 4

Yes. Only if it ends with ABC would it then be correct.

However, I have some problem trimming a Memo field. RTrim does not accept a Memo field and I m wondering how to do the comparison with the white spaces behind.

Can someone help on the trimming of white spaces in a Memo?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Lim,

I just misread your post...

I guess you want to extract the all records when value of your memo column ends with some defined string...(say ABC), then you can use John's solution (just replace IF with WHEN in the CASE statement:):))...

--Ramesh

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Ramesh

Yes, you're right - I corrected it in my SSMS window but then forgot to copy it back into my reply!

Lim

What is a "Memo field"?

John

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

Ramesh Saive

SSC-Insane

Points: 24275

October 17, 2007 at 5:20 am

#742659

John,

I think its a text column...

--Ramesh

Lowell SSC Guru Points: 323546 More actions · Answer 8

hope this helps...this is the model i use for find and replace in text fields.

in this case, the sample is replacing a relative HTML link witha full http: link, but could do exactly what you were asking i think:

the table has an ID column.
DECLARE @reviewid int, @ptr binary(16), @idx int
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1
FROM reviews
WHERE PATINDEX('%href="reviews%',review_body) > 0
WHILE @reviewid > 0
BEGIN
SELECT @ptr = TEXTPTR(review_body)
FROM reviews
WHERE reviewid = @reviewid
UPDATETEXT reviews.review_body @ptr @idx 13 'href="http://www.yoursite.com/reviews'
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1
FROM reviews
WHERE reviewid > @reviewid
AND PATINDEX('%href="reviews%',review_body) > 0
END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sergiy SSC Guru Points: 110209 More actions · Answer 9

Using a relational database would make things a lot more easier...

😉

_____________
Code for TallyGenerator

Lim Chee Hwee-339629 SSC-Addicted Points: 461 More actions · Answer 10

Thanks for all the replies.

John, I meant Memo field as a text column.

From what I saw in the replies so far, there is no way to do a query to match it out? I have to format it into another table first before calling it out to query? It seems like its an awful lot of things to do for finding if the 2 string matches...

Chee Hwee.

Lowell SSC Guru Points: 323546 More actions · Answer 11

can't you test with two clauses in the WHERE statement for a match, and also that it doesn't have the trailing word after it? you need to take the spaces into account, where there is 'ABC[SPACE]' or not, but that's easy enough to do:

CREATE TABLE #Memo (ID int, Memo1 varchar(10), Memo2 varchar(30))

INSERT INTO #Memo

SELECT 1, 'ABC', 'ADGETEQASGEQWT ABC ' UNION

SELECT 2, 'ABC', 'ADGETEQASGEQWT ABC erq3252wr ' UNION

SELECT 3, 'John', 'Hello John' UNION

SELECT 4, 'John', 'Pretty Polly'

SELECT *

FROM #Memo

WHERE PATINDEX('% ABC %',Memo2) > 0

AND PATINDEX('% ABC [a-z,A-Z]%',Memo2) = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lim Chee Hwee-339629 SSC-Addicted Points: 461 More actions · Answer 12

Thanks Lowell!

I just couldn't 'see' that 2 spaces in ' %'. My mind just keep getting stuck in Patindex, Reverse and Rtrim.

It was just a matter of 2 spaces in my query to get the results that I wanted.

Thanks!

Chee Hwee