?
Solved

Insert If Value In Longtext Field Does Not Exist

Posted on 2005-04-04
16
Medium Priority
?
381 Views
Last Modified: 2008-03-10
I don't work with MySQL, so I'm not sure about the exact syntax of this.

What would be the most efficient way to insert a record that contains a longtext field of data into a table if the value of the longtext field does not already exist in the table? I'd like to compare the first 5000 characters of the two longtext fields with all spaces, line breaks, tabs, and carriage returns removed. The value checked and inserted will be coming from an html textarea.

I believe the general syntax should be:

/* need code to create a TempTable that contains a longtext field */
/* need to insert value, removed of all spaces, tabs, line breaks and carriage returns into the longtext field of the TempTable */

INSERT INTO TableName (LongTextFieldName)
SELECT LongTextFieldName FROM TempTable
WHERE NOT EXISTS (
  SELECT LongTextFieldName
  FROM TableName
  WHERE SUBSTRING(TableName.LongTextFieldName, 1, 5000) = SUBSTRING(TempTable.LongTextFieldName, 1, 5000)
  /* need code to do compare the substring of TableName.LongTextFieldName removed of all spaces, etc. */
  )
0
Comment
Question by:DanielSKim
  • 11
  • 5
16 Comments
 
LVL 16

Expert Comment

by:ellandrd
ID: 13701716

/* need code to create a TempTable that contains a longtext field */

try this:

create table tblTempTable (message longtext);

/* need to insert value, removed of all spaces, tabs, line breaks and carriage returns into the longtext field of the TempTable */

try this:

insert into tblTempTable (message) values ('bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah  bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah bah');

then to remove spaces use:

UPDATE tblTempTable SET tblTempTable.Message = Trim([Message]);


0
 
LVL 15

Author Comment

by:DanielSKim
ID: 13701772
thank you for the code.

the only thing else i think i need is the removal of spaces, tabs, line breaks and carriage returns. from other db sql syntaxes, Trim will remove any spaces at the beginning and end of a value.

Is there a Replace function that will remove any spaces, along wtih tabs, line breaks and carriage returns, that exist in the value? If so, it is pretty straightforward to replace the spaces, but what characters need to be used to search for tabs, line breaks and carriage returns, as these may exist since the data is being taken from an html text area?

also, do i need to specifically drop the temp table? or will it be dropped after the transaction comes to an end?
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13701782
remove carriage returns:

UPDATE tblTempTable set Message = replace(Message,chr(10),' ');

This will replace all the carriage returns with a space.
If you just want to remove the carriage return, give ''.

UPDATE tblTempTable set Message = replace(Message,chr(10),'');

Hope this helps...

Ellandrd
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 16

Expert Comment

by:ellandrd
ID: 13701813
<<Is there a Replace function that will remove any spaces, along wtih tabs, line breaks and carriage returns, >>

im am researching for you!
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13701896
chr(9) is the tab character
chr(10) is carriage return


so


UPDATE tblTempTable set Message = replace(Message,chr(9),'     ');
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13701914
after you have updated the "good" table with string containing no spaces, no tabs ect then yes drop the temp table, but what about next time around, when your reading into the "good" mysql table again? you'll need the temp table again...
0
 
LVL 15

Author Comment

by:DanielSKim
ID: 13701918
i don't have access to a mysql database. can you check if something like this works?

SELECT Replace(Replace(Replace(Replace(SomeFieldName,' ',''), Chr(9), ''), Chr(10), ''), Chr(13), '') AS Field1
FROM SomeTableName;

If so, that's the end of my question. Thank you for your help!
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13701936
you say your reading the text from a textarea on a HTML page?

well why not elimate line breaks, tbas and spaces then using some scripting language, then process the data into the mysql db?

what you think?

ellandrd
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13701950
ok i will test, whole on...
0
 
LVL 15

Author Comment

by:DanielSKim
ID: 13702008
The idea is this:

- store the text as it is entered in from the html text area, so that it can be retrieved and displayed exactly as it has been entered it
- check each entry before it is inserted into the table to make sure that value does not already exist in the table. because there may be some variations in spaces, tabs, etc, i want to remove them all and do a check.


assuming what you've provided so far, would this be the correct complete syntax:

CREATE TABLE TempTable (LongTextFieldName longtext);

INSERT INTO TempTable (LongTextFieldName) VALUES ('blah');

INSERT INTO TableName (LongTextFieldName)
SELECT LongTextFieldName FROM TempTable
WHERE NOT EXISTS (
  SELECT LongTextFieldName
  FROM TableName
  WHERE Replace(Replace(Replace(Replace(SUBSTRING(TableName.LongTextFieldName, 1, 5000),' ',''), Chr(9), ''), Chr(10), ''), Chr(13), '') =
Replace(Replace(Replace(Replace(SUBSTRING(TempTable.LongTextFieldName, 1, 5000),' ',''), Chr(9), ''), Chr(10), ''), Chr(13), '')

DROP TABLE TempTable

0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13702218
if you are storing the text as it is entered in from the html text area, then why remove all spaces, tabs etc from it.

why noit just read it in the way it is as you said?  do you plan to read the text bk to a webpage again?

i still think the best way is to removing all break lines, spaces, tabs in the webpage first then read the value into mysql db...

this way you have more functions fexiablity when using a scripting language...

but again i dont understand why you want to

store the text as it is entered in from the html text area, but still remove all tabs spaces etc... dont make sense to me!

Ellandrd
0
 
LVL 15

Author Comment

by:DanielSKim
ID: 13704283
I only want to remove them in the where clause but not when I insert the data into the field. The point is to compare the value from the text area with the value in the field of the table. If the user enters a space before the value that they copy and paste, or enters extra line breaks, i don't want that to influence the comparison of the values.

So, what do you think? Is this going to work?

CREATE TABLE TempTable (LongTextFieldName longtext);

INSERT INTO TempTable (LongTextFieldName) VALUES ('blah');

INSERT INTO TableName (LongTextFieldName)
SELECT LongTextFieldName FROM TempTable
WHERE NOT EXISTS (
  SELECT LongTextFieldName
  FROM TableName
  WHERE
    Replace(Replace(Replace(Replace(SUBSTRING(TableName.LongTextFieldName, 1, 5000),' ',''), Chr(9), ''), Chr(10), ''), Chr(13), '') =
    Replace(Replace(Replace(Replace(SUBSTRING(TempTable.LongTextFieldName, 1, 5000),' ',''), Chr(9), ''), Chr(10), ''), Chr(13), '')
);

DROP TABLE TempTable;
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13706331
whywouldyouwanttoremoveallspacesfromaparagraphoftextthatyou'regoingtostoreinthedatabase?

thatwouldmakethetextreallyhardtoread
0
 
LVL 16

Accepted Solution

by:
ellandrd earned 2000 total points
ID: 13706399
to remove spaces:
UPDATE TempTable SET LongTextFieldName=REPLACE(LongTextFieldName, ' ', '');

to remove newline etc:
UPDATE TempTable SET LongTextFieldName = replace(LongTextFieldName, "\r", "");

to remove tabs etc:
UPDATE TempTable SET LongTextFieldName = replace(LongTextFieldName, "\t", "");

try this: it should work....

INSERT INTO TableName (LongTextFieldName)
SELECT LongTextFieldName FROM TempTable
WHERE NOT EXISTS (
  SELECT LongTextFieldName
  FROM TableName
  WHERE
    Upper(Replace(Replace(Replace(Replace(SUBSTRING(TableName.LongTextFieldName, 1, 5000),' ',''), Chr(9), ''), Chr(10), ''), Chr(13), '')) =
    Upper(Replace(Replace(Replace(Replace(SUBSTRING(TempTable.LongTextFieldName, 1, 5000),' ',''), Chr(9), ''), Chr(10), ''), Chr(13), ''))
);
0
 
LVL 15

Author Comment

by:DanielSKim
ID: 13706526
again, i am not removing those characters to store in the database; i am removing them to compare the value of the textarea and values in the field for the table, in order to ensure that comparisons of the text are not skewed because of extra spaces or line breaks, though the actual text is the same. that is why i am not taking them out when i am inserting them either in the temp table or the main table. this is only done in the the where clause.

thank you again for your help. very much appreciated.
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 13706854
OK your the boss!

Ellandrd
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question