CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.
My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).
When I loop through the data to send, I run:
UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35
The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.
The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.
Using 4k blocks I end up with 419k of data in the field when all is said
and done.
View Complete Forum Thread with Replies
Related Forum Messages:
CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done. Please let me know if/when this will be fixed, and if there is a work around that might be used, r a better way to insert BLOB data is known.
View Replies !
Function Concat Does Not Exist
INSERT INTO `tbl_x` SELECT concat (substring(@count_date, 1, 4), substring(@count_date, 6, 2), substring(@count_date, 9, 2)) as count_date, concat (country_number) as country_number, concat (city_number) as city_number, concat (shop_number) as shop_number, concat (booth_number) as booth_number, concat (substring(food_number, 3, 8)) as food_number, concat (sum(food_amount)) as food_amount, concat (sum(total_account)) as total_account, concat (sum(rebate_account)) as rebate_account, 0 FROM tbl_y WHERE BusinessDate = @count_date and count_date = @count_date GROUP BY country_number, city_number, shop_number, booth_number, food_number;
View Replies !
Illegal Mix Of Collations When Using The Concat Function
SELECT concat('hello world ', cui) FROM `umls_cuis` I am using MySQL 4.1.3-beta. I get the following error when trying to execute the above SQL: ERROR 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'concat' What can I do about it?
View Replies !
Field Not Updating Using CONCAT() Function When Field Is Empty
i have used the mysql CONCAT($existingvalue, $newvalue) function as part of an update query where the input given is suppose to concatenate with the existing value of the field in the database. this works only when there are value/s in that field, when there is know exising value in the field the update query does not work on that field. how ever update on any other field works fine(not using the function) at the same time.
View Replies !
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
View Replies !
WHERE With CONCAT
I have a table A with a (varchar 20) A.field and a table B with two (varchar 10) fields: B.field1 and B.field2. How can I make a SELECT with a WHERE of this type: WHERE A.field = CONCAT (B.field1 , B.field2) I get an error in CONCAT. How is the string concatenation in mySQL?
View Replies !
Using CONCAT
Is it possible to make query which would select some value even column would be NULL/EMPTY ? Example: Table name John NULL Mike SELECT CONCAT_WS('something',name) FROM table; results: Johh Something Mike
View Replies !
Use CONCAT
I'm having lots of problems using the CONCAT instruction. I'm trying to store in a variable (@f) a COUNT, but the system always crashes and I don't know why. Here you have the lines: set f=0; set @f:=f; set @stmt3:=CONCAT("select count(*) into `",@f,"` from `",@n,"` where sourceurl=`",@b,"` and link=`",@c,"`"); prepare query from @stmt3; execute query;
View Replies !
Using Concat()
I'm trying to combine 2 fields into 1 new field using concat. This is what I using UPDATE 'idxactrs' SET 'idxactrs.desc' = concat(remarks1,remarks2) It just says "You have an error in your SQL syntax near ''idxactrs' ...
View Replies !
CONCAT All Columns
I want to produce a query that does something like this: SELECT MD5(CONCAT(*)) FROM table WHERE 1 * is all the columns. I don't want to have to type in every column name, but have MySQL do it.
View Replies !
Concat Two Tables
I have two tables, one containing payments to a supplier and one containing invoices issued by a supplier. I'd like to get a total from each table with one query. I need somehow to list all payments and all transactions together in one table result set. i.e.....
View Replies !
Condition In CONCAT
i have three fields of name in database named as fnam,mname,lname. fname and lname is mandatory field and mname is optional. i want to make a search query on name Expand|Select|Wrap|Line Numbers
View Replies !
Concat A Field
I'm trying to take the itemid field and add a "9" in front of it and make sure it's a 5 digit field and copy this new number to the Allocate_To field. I have the below but if the itemid field contains "130" it concats to "9130" and not "90130" update equipment set Allocate_To = left(concat('9',cast( itemid AS char)),5)
View Replies !
DISTINCT And/or CONCAT?
1 Table, 3 Columns Language1 | Language2 | Language3 French......| Spanish Spanish.....| German Spanish.....| French......| Japanese Spanish.....| French What I want is a concatenated list of the languages from all three columns (i.e. French appears once, etc.). What I tried: SELECT GROUP_CONCAT(DISTINCT mi_language1, mi_language2, mi_language3) as mi_language FROM las.mi_languages
View Replies !
Group Concat
I am looking for a way to return the COUNT of the result set of my GROUP_CONCAT query. I currently get a result set and that's fine but instead of returning the actual array I just want the number of objects in the array. I tried COUNT(GROUP_CONCAT(DISTINCT scores)) AS test_scores but it didn't work.
View Replies !
UPDATE With Concat
UPDATE table SET field='select concat(field1,field2) from table' This gives me a zero in my field I can select concat separately without any problem
View Replies !
Concat () Problem
I'm having problem using concat select CONCAT(firstname,' ', lastname) as fullname from tbltable where fullname = 'name'; It say Unknown column 'fullname' in 'where clause'. It is my sql wrong or what?
View Replies !
Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear) FROM tblmonths, tblyears where CONCAT(tblmonths.fldID," ", tblyears.fldYear) NOT EXISTS (SELECT CONCAT(fldMonth, " ", fldYear) from tblexpensesclaims) GROUP BY tblyears.fldYear, tblmonths.fldID
View Replies !
Problems With Concat
I'm having problems with Concat. I have this piece of code: set @m:=a; SET @stmt1:=CONCAT("INSERT INTO ",@m," VALUES (b,c)"); PREPARE query from @stmt1; execute query; b and c are cursors and when it tries to execute the query and I get the following error: "Unknown column 'b' in field list" It seems that is not doing correctly the CONCAT?
View Replies !
CONCAT And Variables
I have the following statement: ------------------ SELECT CONCAT('hxxp://www.whatever.com/all.html?t=4&p=1&u=',@c) into @c; set @stmt3:=CONCAT("INSERT INTO webs values ('",@c,"')"); prepare query from @stmt3; execute query; -------------- I think that I'm using correctly the CONCAT function but it doesn't work.
View Replies !
CONCAT Not Working
This query seems structurally correct to me but the mysql query builder says it is not! SELECT CONCAT (firstname, lastname) as fullname FROM candidates WHERE candidateid = 12345 Any ideas whats wrong with it?
View Replies !
Concat A Set With A Select
i have a set x{1,2,3,4,5} and I want to make a query like this: select id from table 1.. where id in x{} and .... limit n I want to in case the select return an empty set (because the where sentence) return the x set or if the select count() is less than "n" wanted to completed with the x set. Besides, i dont' want to make nested query because performance.
View Replies !
What's Faster With LIKE & CONCAT?
I have a quick question about searching with LIKE. I'm using InnoDB type tables because I will be doing a LOT of inserts in relation to selects. (Lots of storage, seldom selecting to view.) I need to search 4 different columns as a part of a search query. However, 3 of these columns are very short--VARCHAR(150), VARCHAR(63), VARCHAR(10)--and one column of VARCHAR(4000). Currently I have ....
View Replies !
Concat Two Columns
I am very new in MySQL. How to concat two columns(different tables)? Can anyone help me. Thanks! Is This correct? SELECT Concat_WS('/',A.SPACEKEY,B.TITLE) Title FROM SPACES A, CONTENT B WHERE A.SPACEID = B.SPACEID.
View Replies !
Concat For Numbers
I am interested in performing a concat for numbers with a space e.g. select concat (num1, ' ', num2) from tablex as numconc such that if num1 is 123 and num2 is 456 the result would be '123 456' I believe concat should work for a string rather than number format, however having created the fields num1 and num2 in string format it still does not work. Any advice on why this isn't working, or an alternative method to achieve the result would be most welcome.
View Replies !
Problem With Concat
I have the following select: SELECT distinct name, main.ACCOUNT, concat(FlatNo,' , ',FlatName,' , ',number,' , ',`St Name`,' , ',SuburbName,' , ',`Town Name`,' , ',Stand) AS Address FROM ... I need to return all the available address information that we have but the select returns only about 15 addresses in a DB of about 100000. Some of the address fields do contain null values and the SELECT CONCAT is returning null for the whole row if any field contains a null value. Is there anyway round this? I have tried IFNULL(Field,"") inside the concat but this does not seem to work either.
View Replies !
Update Using Concat
I have a form that I call for the data from a database table. Once the table is filled with data, the viewer has the option to add data to one field in the form. I want to be able to add the new comments without losing the old comments in the db column. Field A has the existing data ($Remarks) Field B has the new comments ($NRemarks) I have tried various CONCAT statements but none of them work, and I am thinking that this may be because Field B is not a column in the DB, as it is new information being added. Is there a way to keep the data in the DB column called Remarks and add the information in Field B to it on a seperate line. I don't know if there is a command like append or edit or add to, that will add information to the existing information. I have tried the following: $sql="UPDATE 'workorder' SET Remarks CONCAT (Remarks, NRemarks)"; ?> $sql="UPDATE 'workorder' SET Remarks CONCAT ($Remarks, $NRemarks)FROM workorder WHERE Work = '$Contact'"; ?> $sql="UPDATE `workorder` SET Remarks = CONCAT(SELECT Remarks FROM workorder WHERE Work = '$Contact', '', '$NRemarks') WHERE Work = '$Contact'"; ?>
View Replies !
How Does Mysql_field_len Act On CONCAT?
I am quite puzzled by this. I have two fields which if selected by themselves will return a field length of 2 using mysql_field_len. However if I select them as CONCAT(field1,' ',field2), the field length is much greater than the 5 I would expect. It looks to be about 20. Is there a way to control this length? Where is it getting the field length from? EDIT: Nevermind, this can be done using CAST(CONCAT(field1,' ',field2) AS CHAR(5))
View Replies !
Using A WHERE Clause On A CONCAT
I currently have the query SELECT CONCAT_WS( ' ', first_name, last_name ) AS full_name, username, country, region, CONCAT_WS( ', ', region, country ) AS location FROM people which works fine but when i try to put a WHERE clause on full_name it returns an error. Is there any way to perform a WHERE on this concatenated string.
View Replies !
Concat In Select Statement
Is it possible to conduct a test in the select statement to see if a value is not found in one table (t2) then get the value from another table (t3). In this case you would have 3 tables. if there is no related record in table2 t2, then get the value from table3 t3 If so what would the code look like? .....
View Replies !
INSERT With Concat Auto_increment
I have a table that stores online orders. Each row in the table uses auto_increment to create a unique id for each order, in a field "id". Each order (row in the table) also has a serial_number field that has a string representing the product and a unique number representing the order. I would like to append the id field value for the newly created order to the product string, to create the serial number. Can this be done within a single SQL statement or do I need to insert the order, find out the id, then update the order and appending the id on the serial number string. Is it possible to append the id of the newly inserted row to another field in the row in a insert statement? I tried INSERT INTO orders SET serial_number = CONCAT('product text',id); but MySQL did not like the id column in the concat function. Do I need to use LAST_INSERT_ID to find the id?
View Replies !
Problem W/concat To Date
I am trying to run a report that displays data that falls between a date range. The dates are entered in mm/dd/yyyy format. I convert the date using ColdFusion code for MySQL to use: cfset Start1c = DateFormat(#Start1#, "yyyy/mm/dd") cfset End1c = DateFormat(#End1#, "yyyy/mm/dd") The query that uses this variable will run as long as I, 1. use slashes in the date variable above 2. query for data that falls in the same year Here is the part of my query that has a problem w/the formatting of this date: date_format(date_sub(concat_ws('-',dataYear,dataMonth,'02'), interval 1 day), '%Y-%m-%d') BETWEEN '#Start1c#' AND '#End1c#' AND InputMethod = 1 dataYear and dataMonth are integer fields combined to create a date.
View Replies !
CONCAT Length Limitation
I have encountered a problem while using the CONCAT command to update a column with a size over 1mb, the existing information is lost. I can't seem to find a suitable alternative to update a field in a row with several appends. the data length in total will be exceeding over 16mb at times.
View Replies !
GROUP CONCAT Issue
Iam using Mysql Version 5.0. the query, SELECT GROUP_CONCAT(field_name)FROM TABLE1; This returns BLOG as result set in the editor in my Toad. What is the issue, i have just 300 records in my table. Why this happens, please help
View Replies !
Need To Concat Column And DATE_FORMAT
I'd like to combine the title and starttime so I can distinguish amongst several requests with the same title. ie: ID=7116 name=Testing11-26-2007 Here's my query: select ID, title+DATE_FORMAT(starttime,"%m-%d-%Y") as name from requests where ID=7116 Which gives me: ID=7116 name=11 What do I need to do to get the result of DATE_FORMAT into a form that will concat with the text from another column?
View Replies !
Concat 2nd Table Results Into First?
Given three tables used to track multiple email addresses and phone numbers for a person: Table 1: id, name, address Table 2: table1_id, email Table 3: table1_id, phone How can I do a select to end up with all emails and phones concated together into a single row per individual: id, name, address, "email,email,email", "phone,phone" The solution needs to work with MySQL 3.23 (or possible MySQL 4.0). 4.1 is NOT an option, so group_concat is out.
View Replies !
Nesting A 'concat' Within A 'replace'
I have the_table: col_1 , col_2 ------------------------- aaa , aaa_do aaa_dat the query that I am using:"update the_table set col_2=replace(col_2,col_1,concat('hoorah_' , col_1) ) " I want col_1 , col_2 ------------------------- aaa , hoorah_aaa_do hoorah_aaa_dat however the query does not successfully stick the 'hoorah_' in front of the aaa_dat; instead I get this: col_1 , col_2 ------------------------- aaa , hoorah_aaa_do aaa_dat
View Replies !
CONCAT () Returns BLOB
MySQL ver. 3.23.49 SELECT tblHrsClass.classid , CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl FROM tblHrsStudent LEFT JOIN tblHrsClass ON tblHrsStudent . classid =tblHrsClass.classid GROUP BY tblHrsClass . classid HAVING COUNT(tblHrsStudent . classid )<19 The query above is broke. I'm using it to build a list box. I can run the query without the CONCAT function and pulls the expected data. When I add CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl, class_ttl is returned as a BLOB of a certain size.
View Replies !
Concat A Field In Up To 4 Rows?
A customer can have between 1 and 4 rows, is it possible to make a query that will merge field "A" of all rows and then discard all other rows leaving only 1 row left with the merged data in field "A".
View Replies !
SELECT Subquery CONCAT
SELECT * FROM (SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1') The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like: SELECT * FROM posts How do I perform a string concatenation during a SELECT query with MySQL?
View Replies !
SELECT CONCAT() Subquery
SELECT * FROM (SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1') The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like: SELECT * FROM posts How do I perform a string concatenation during a SELECT query?
View Replies !
Help With Subquery / Group Concat
I've attached some data from which I want to extract individual itinerary ids and show the ships which go to those destinations. The problem is that the destination ids are stored as comma separated values in a single field and there are rows which duplicate the same associations between the ship and destination ids. I can do: SELECT ship_id, GROUP_CONCAT(DISTINCT destination_ids) FROM itineries GROUP BY ship_id; and get a list of destination ids for each ship but what I actually want is a list of ships for each destination Id. Can somebody give me some pointers on how to achieve this? I think it might be possible with a subquery but am not sure how to go about this. MYSQL version is 4.1.20.
View Replies !
Concat Two Fields With The Same Name (using A Join)
I have a master query in my PHP that's fetching all possible info about a set of book publications. A few types of books have additional classifications, so I broke that data out into separate tables. But I am joining all of those tables to get all info at once. I have two tables with a field `current` and both tables are part of my join. The catch is, only one of the two will be populated in each returned record and the other will be blank. In order to get `current` back and not `table1_current` and `table2_current` I thought I could concat the two fields so I would be sure to get the value. The problem is, I'm adding ``CONCAT(table1.current, table2.current) as current`` to my query, and `current` is coming back blank. If I select each table's `current` separately (e.g. table1.current as table1_current, table2.current as table2_current) `table1_current` comes back as `yes` while `table2_current_ comes back as blank. So I know the strings are not blank. Here is my query: Code: SELECT r.resource, p.resource_id, p.primary_topic_id, p.primary_user_id, p.title, p.in_press, p.pub_year_start, p.pub_year_end, p.cite, p.description, p.status, p.show_dept, p.show_search, p2u.show_homepage, u.user_id, fp.investigators, DATE_FORMAT(fp.start_date, '%m/%d/%Y') as start_date, DATE_FORMAT(fp.end_date, '%m/%d/%Y') as end_date, fp.sponsor, fp.grant_number, fp.funded_amount, sa.first_name, sa.last_name, sa.program, sa.advisors, CONCAT(fa.organization, ea.organization, lr.organization) as organization, CONCAT(fa.division, ea.division, lr.division) as division,.....
View Replies !
Create & Store A Concat String
I have two tables: keywords(itemID, WordID) and words(WordID,Word) I would like to create a single string of the words associated with a single ItemID by concatenating words and store them into a new table wordstring(itemID, wordstring).
View Replies !
Help With UPDATE Using CONCAT On Longtext Fields
I have two tables with longtext fields that I'd like to concatenate and update in one of the tables. Not sure if you can use concat on a longtext field. Here's what I tried: UPDATE `node_revisions`, `weblink` SET `node_revisions`.`body`=concat(`node_revisions`.`body`,"<br /><a href="",`weblink`.`weblink`,"">Visit this site</a>") WHERE `node_revisions`.`nid` = `weblink`.`nid`; This is the scenario. I have these 2 tables: node_revisions table ----------------------------- | id | nid | body (longtext) | ----------------------------- | 1 | 23 | this is some text | ----------------------------- weblink ----------------------------- | id | nid | weblink (longtext) | ----------------------------- | 5 | 23 | http://somesite.com | ----------------------------- I'm trying to use UPDATE to concatenate the node_revision.body and weblink.weblink fields, where they share foreign key nid. So the resulting node_revisions would look like: node_revisions table ----------------------------- | id | nid | body (longtext) | ----------------------------- | 1 | 23 | this is some text<br /><a href="http://somesite.com">Visit this site</a> | ----------------------------- When I run the UPDATE statement above, I get the following feedback, but nothing has in fact changed: Query OK, 813 rows affected (0.15 sec) Rows matched: 813 Changed: 813 Warnings: 0
View Replies !
|
TRACKER
