Questions tagged [concat]

In formal language theory and computer programming, string concatenation is the operation of joining two character strings end-to-end

Filter by
Sorted by
Tagged with
6 votes
1 answer
886 views

How to concatenate two column of different types in sql? (without getting some strange warning)

When I try to concatenate two columns, of different type, I get the following warning: Type conversion in the expression (CONVERT_IMPLICIT(varchar(41), [TABLE].[COLUMN], 0)) can affect "...
G. Ciardini's user avatar
0 votes
0 answers
139 views

How to use variable in the select query PostgreSQL

I'm looking for a way to write a recursive query, but I'm getting an error. "SQL Error [42601]: ERROR: syntax error at or near "with recursive" Position: 287" query result "...
AleksTr's user avatar
0 votes
0 answers
254 views

Replacing Part of a string within a group Concat

I have part of a string that I would like to remove when selecting. The Problem is that there are a few pieces within the string that changes. This is what I have so far. Select GROUP_CONCAT(...
Joey Freeman's user avatar
0 votes
1 answer
606 views

How to prevent CONCAT function adding space in Postgresql?

Postgresql-11 select tick_time, nano_secs, concat( to_char(tick_time, 'MMDD HH24:MI:SS.US'), to_char(nano_secs, '000') ) from ticks order by tick_time, nano_secs limit 100; I want to ...
Leon's user avatar
  • 305
0 votes
1 answer
74 views

Replace %S with Feature Value on Feature String Based on Feature Name and Partid

I need to write a select statement that will replace a %S string in the FeatureString column with the actual values stored in the same table for a given PartID. With the following example data: +------...
ahmed barbary's user avatar
0 votes
1 answer
2k views

child to parent in same table - there must be a better way to do this

i want to relate child to parent from same table. Table is built like this. id, parentid, name i want to have the complete path of the [name] columns without specify levels like i have in the example. ...
wiper's user avatar
  • 3
2 votes
3 answers
2k views

How to find data type of output in MySQL?

I was reading about the CONCAT() function, and I noticed this: mysql> SELECT CONCAT(14.3); -> '14.3' While running this command in MySQL 8.0 Command Line Client, the output didn't have ...
Random Person's user avatar
0 votes
2 answers
114 views

Creating an if then clause in SQL

I'm fairly new to SQL and I am am used to writing formulas in Crystal Reports. I want to recreate the following formula from Crystal Reports into an SQL statement to create a column: if (not isnull ({...
Ariane Loersch's user avatar
0 votes
1 answer
734 views

How does CONCAT format date fields?

I have the following code : start transaction; show datestyle; set datestyle to DMY; show datestyle; select concat(timestamp_field, '') from my_table ; commit; It outputs this : ...
Stephan's user avatar
  • 1,443
1 vote
1 answer
772 views

Invalid Column Name on CONCAT_WS

Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue. I have two tables cases and legal_entity the data on the two tables looks like this legal_entity ...
Cistoran's user avatar
  • 113
1 vote
2 answers
1k views

How to use GROUP_CONCAT for a column and COUNT(*)?

I get the ids of a query by GROUP_CONCAT. SELECT GROUP_CONCAT(ArticleID) FROM TagMap WHERE Tag IN(...) How can I merge this query with a COUNT(*) group by SELECT ArticleID,COUNT(*) FROM TagMap WHERE ...
Googlebot's user avatar
  • 4,491
0 votes
1 answer
1k views

What is the best way to handle a join / group_concat query in MySQL

I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation. I have 3 tables: tasks, assignees, and users ---tasks---- id, task, completed ---...
Ben's user avatar
  • 101
1 vote
2 answers
119 views

Best Practices for Separating Content from Presentation in SQL

A common and useful paradigm of programming is to separate content and presentation. One system deals with building the result, another system deals with outputting it. You see this with HTML vs CSS. ...
pbarney's user avatar
  • 161
0 votes
1 answer
526 views

Concatenation of varchar-converted DATEDIFF and placing a string afterwards - issues?

Everyone, the table relationship diagram for the database is the below one In case the full DDL for the database is requested, I can provide it (although it will probably exceed the character limit ...
query_question's user avatar
0 votes
1 answer
638 views

Concatenate in MS Access - how to omit space and comma if first value is missing?

I am concatenating 2 values in a MS Access Report: "Method" and "Experiment" and they are to be separated by a comma (,). I have figured out how to concatenate succesfully when the second value is ...
Geo_explorer's user avatar
-1 votes
1 answer
147 views

Concat digits into xxx-xxx form

Example, I get data from eclipse and insert into 'abc' table. so, now how I can get the data from the 'abc' table and concat the digit? Example: I enter 123456 then in SQL need to concat into format ...
lovely's user avatar
  • 1
0 votes
1 answer
31 views

How to concatenate these two statements (RIGHT and LEFT)? I am trying to trim two characters off both the beginning and end of cell

Is it possible to either combine or concatenate the following two statements? RIGHT(wp_postmeta.meta_value, LENGTH(meta_value) -2) LEFT(wp_postmeta.meta_value, LENGTH(meta_value) -2) What I am ...
user2461674's user avatar
0 votes
1 answer
241 views

Dynamic Pivot Table syntax error

What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this. mysql> SET @sql = NULL; Query OK, 0 rows affected (0.00 sec) mysql> SELECT -> GROUP_CONCAT(DISTINCT ...
dan-klasson's user avatar
0 votes
1 answer
929 views

Replace part of a string in MYSQL, when only the substring's starting characters, ending characters & a unique string in the middle is known?

I'm trying to find a way to replace part of a string in a database without knowing the exact substring content other than how it starts, ends and has a specific ID string somewhere in between that. ...
Josh979's user avatar
  • 103
3 votes
3 answers
748 views

Why does the concatenation order change with a User-Defined Table Type?

I've encountered what seems to me to be a strange ordering issue when using CONCAT with a User Defined Table Type. I've put the SQL below that allows me to reproduce this: I have the following user ...
Interminable's user avatar
-2 votes
1 answer
2k views

How to concatenate rows into one comma seperated value when used with case statements

I have a query that has multiple case statements. I am trying to reduce the results to one row per USER_ID that has one of the columns (specialty_id) comma separated because some users have more than ...
steves86ta's user avatar
8 votes
2 answers
2k views

NULL value causes blank row in SELECT results for text concatenation

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results: postgres=# SELECT ' (' || obj_id || ', ''' || ...
Randall's user avatar
  • 345
0 votes
1 answer
464 views

How to use query result as REPLACE parameters

i'm trying to: 1- Find a string inside a lot of content 2- Replace that specific part of the string with a new one using REPLACE function like this: REPLACE(column_name, query#1, query#2) Is this ...
gallo2000sv's user avatar
0 votes
1 answer
64 views

Postgresql Date Function Returning Range In Readable Format

I am wondering if it is possible to use postgresql's date-time , concat and case functions to give one of the following results: June 4th to 11th 2019 (where the 1st and 2nd date are within the same ...
Ron Piggott's user avatar
0 votes
1 answer
2k views

Sql subquery for nested parent child data

I have two tables , on parent table (categories ) and child table (features).. I have this query select c.* , (select GROUP_CONCAT(f.featuresId , f.featuresTitle ) from ...
Arash Ammarlooi's user avatar
0 votes
0 answers
62 views

How performant is finding a substring in database?

To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item. For example: I have a request to /animals/dog/darktheme/. The ...
Reed's user avatar
  • 111
3 votes
1 answer
3k views

Large string concatenation in query

I am trying to concatenates strings in T-SQL like 'This is a test ' + CHAR(13) + CHAR(10) + ' string' When the string is long (I do not know the limit), I get an error: Some part of your SQL ...
Anders Lindén's user avatar
0 votes
1 answer
737 views

CONCAT_WS causes "Data too long" which works partially?

I've got a simple table with several columns and I'm trying to merge two of them into one. I'm working with MySQL 5.5 and I can reproduce the error one a specific data set by running the following (...
TragedyStruck's user avatar
2 votes
2 answers
5k views

How can the concatenatation of a text and a NULL value return a non-null result?

Assert 1 The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the ...
Erwin Brandstetter's user avatar
2 votes
2 answers
2k views

How to Concat Datetime without Losing Second Precision

What is best way to Concat on datetime column with other fields, without losing/truncate millisecond precision? Example: We have a column in datetime precision. 1988-02-27 00:00:04.000 We are ...
user avatar
-2 votes
1 answer
18k views

Updating a string field by concatenating 2 int columns

I have 2 columns, an int and a tiny int, and I want to update another column, a varchar one, with the value column1 + '-' + column2. Something like this pseudo code: UPDATE COLUMN3 varchar = CONCAT (...
alish's user avatar
  • 37
0 votes
1 answer
2k views

Concatenate into Field in Update

I am trying to write a Stored Procedure which updates a quantity data column in one of my tables, and then takes a field set for random notes and concatenates the update information. This is what I ...
Skitzafreak's user avatar
1 vote
1 answer
1k views

SQLite3 doesn't have concat_ws, looking for workaround

I'm trying to concatenate some columns and I would like to use space as my output string delimiter. Basically concat_ws would be a perfect fit, but it doesn't exist for SQLite3. I'm inside a ...
J. M. Becker's user avatar
1 vote
2 answers
2k views

How to Concatenate DateTime to nvarchar field

I have a table with a nvarchar(32) column and I want to update the value in this column with the first 10 bytes of another field on the table which is defined as nvarchar(32) after the 10 bytes I want ...
Jason's user avatar
  • 69
1 vote
1 answer
318 views

Removal of blank lines

I am creating a SSRS Report to produce some address labels. I have a SELECT statement as follows: SELECT DISTINCT AddressLink.intAddressID, Addresses.txtLabelSalutation, ...
MIS_Gem's user avatar
  • 151
12 votes
2 answers
18k views

Conditional string concatenation in PostgreSQL

I have a table parcels which currently contains the columns owner_addr1, owner_addr2, owner_addr3. Sometimes, one or both of the latter two fields is empty. I want to combine them into a single new ...
J. Taylor's user avatar
  • 379
-1 votes
1 answer
7k views

Update a table using values from another table

I have two tables with structure Table1 ------ No | Name | IDNo | HouseNoBlock Table2 ------ IDNo | HouseNo | Block I am trying to update the HouseNoBlock Column of first table with value ...
Anoop D's user avatar
  • 155
2 votes
1 answer
6k views

MySQL "Out of sort memory" error on a simple GROUP_CONCAT query

I have a (what it seems to me) simple query which fails to run on a simple table, the error is: Out of sort memory, consider increasing server sort buffer size These are the SQL statements which I'...
ekstrakt's user avatar
  • 121
3 votes
3 answers
596 views

Array to string sometimes concatenates fewer values

array_to_string(array_agg(array_to_string(array[ highlights,exceptions,inter_note,inter_status::text ,comp_note,comp_status::text,hard_note,hard_status::text] ,'|#|'::...
Namburi Akhil's user avatar
1 vote
1 answer
8k views

Optimizing query. FOR XML PATH or some alternatives?

Is there any way to optimize the FOR XML PATH statement? Or maybe I should use another approach? Current approach is not acceptable at all. It takes minutes. I know view is a really big union and it ...
p__d's user avatar
  • 113
0 votes
2 answers
79 views

Is there any way to concatenate a value in a column with different strings?

As you can see on the column 'Race Position' I have a set of values corresponding to the participants results. 1 being 1st Position and so on. What I wanted was to be able to concat the values and ...
HeadsR'Us's user avatar
3 votes
1 answer
2k views

How do I build a PostgreSQL ORDER BY expression based on a concatenation of columns?

I'm using Postgres 9.5. I want to write an order by expression for a series of concatenated strings, but I'm not sure if that's possible or how to do it. I have cobbled together the below myproject=...
Dave's user avatar
  • 643
1 vote
1 answer
6k views

Loop inside of query in mysql procedure. Is it possible?

I have the following query: INSERT INTO major (key_1, key_2, ... , key_N) VALUES (v_1_1, v_1_2, v_1_3, ... , c_1_N) (v_2_1, v_2_2, v_2_3, ... , c_2_N) ... (v_K_1, v_K_2, v_K_3, ....
Daniel's user avatar
  • 209
2 votes
2 answers
10k views

Query with join and concatenation of one joined table's column

I have the following structure: Table R Table RZ (n-m) Table Z ------- ----------------- --------- R1 R1 | Z1 Z1 | "A" R2 R1 | Z2 Z2 | "B" R3 ...
Magier's user avatar
  • 4,757
0 votes
1 answer
124 views

Concatenating Multiple Columns in TSQL

I have the following data JoinFlag ReferenceType ReferenceValue A CARRIER Sample####### A LEASE_NUMBER 17438 A LEASE_NUMBER 6054 A ...
Sudhir Kumar's user avatar
0 votes
2 answers
173 views

Elegant way for concatenation

I am looking for the most elegant solution to prevent the addition of the last/ final separator ';' after the last row. For example the result should be 'Peter,Paul,Jill' instead of 'Peter,...
Magier's user avatar
  • 4,757
0 votes
1 answer
12k views

SQL server Like pattern match with CONCAT() always fails

I am facing problem in a simple query script for SQL server. Please help The script is CREATE PROCEDURE [dbo].[Search_By_Author_And_Name] ( @Author_name AS [varchar](50) ) AS BEGIN Select B....
Debesh Mohanty's user avatar
3 votes
4 answers
34k views

MySQL 5.7 JSON_MERGE combine with GROUP_CONCAT

we have table with JSON column for example simple: {"a": 1} {"b": 2} run JSON_MERGE query select JSON_MERGE('{"a": 1}','{"b": 2}') result correct: {"a": 1, "b": 2} run the GROUP_CONCAT query ...
a_vlad's user avatar
  • 3,635
2 votes
1 answer
118 views

How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON

Finally, none of our customers are using SQL Server 2005 anymore, so we can drop support for it and start using all those awesome "new" SQL Server 2008 features (indexed views, filtered indexes, query ...
Heinzi's user avatar
  • 3,145
20 votes
2 answers
877 views

Why does Concatenation operator estimate fewer rows than its inputs?

In the following query plan snippet, it seems obvious that the row estimate for the Concatenation operator should be ~4.3 billion rows, or the sum of the row estimates for its two inputs. However, an ...
Geoff Patterson's user avatar