?
Solved

Insert If Value In Longtext Field Does Not Exist

Posted on 2005-04-04
16
Medium Priority
?
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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

800 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