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
0 votes
1 answer
55 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: +------...
user avatar
0 votes
1 answer
103 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. ...
user avatar
1 vote
2 answers
49 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 ...
user avatar
0 votes
2 answers
64 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 ({...
user avatar
0 votes
1 answer
50 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 : ...
user avatar
  • 1,263
1 vote
1 answer
306 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 ...
user avatar
  • 113
1 vote
2 answers
339 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 ...
user avatar
  • 4,179
0 votes
1 answer
440 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 ---...
user avatar
  • 101
1 vote
2 answers
75 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. ...
user avatar
  • 161
0 votes
0 answers
131 views

How to avoid GROUP BY on one field in Oracle SQL

i'm using a query : SELECT t.tID, min(t.CONTEXTTIMESTAMP) contextTime, CONCAT(' ',CONCAT(ac.NAME, CONCAT('=',ac.VALUE))) as idf FROM TABLE1 t INNER JOIN ( select t....
user avatar
0 votes
1 answer
193 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 ...
user avatar
0 votes
1 answer
182 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 ...
user avatar
-1 votes
1 answer
84 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 ...
user avatar
  • 1
0 votes
1 answer
24 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 ...
user avatar
0 votes
1 answer
142 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 ...
user avatar
0 votes
1 answer
742 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. ...
user avatar
  • 103
3 votes
3 answers
397 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 ...
user avatar
-2 votes
1 answer
635 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 ...
user avatar
6 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 || ', ''' || ...
user avatar
  • 309
0 votes
1 answer
57 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 ...
user avatar
0 votes
1 answer
26 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 ...
user avatar
0 votes
0 answers
959 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 ...
user avatar
0 votes
0 answers
32 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 ...
user avatar
  • 111
3 votes
1 answer
2k 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 ...
user avatar
0 votes
1 answer
523 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 (...
user avatar
2 votes
2 answers
2k 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 ...
user avatar
2 votes
2 answers
1k 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
11k 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 (...
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 ...
user avatar
1 vote
1 answer
890 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 ...
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 ...
user avatar
  • 69
1 vote
1 answer
166 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, ...
user avatar
  • 151
9 votes
2 answers
13k 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 ...
user avatar
  • 317
-1 votes
1 answer
6k 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 ...
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'...
user avatar
  • 121
3 votes
3 answers
475 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] ,'|#|'::...
user avatar
1 vote
1 answer
7k 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 ...
user avatar
  • 113
0 votes
2 answers
70 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 ...
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=...
user avatar
  • 605
1 vote
1 answer
5k 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, ....
user avatar
  • 187
2 votes
2 answers
8k 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 ...
user avatar
  • 4,605
0 votes
1 answer
67 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 ...
user avatar
0 votes
2 answers
148 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,...
user avatar
  • 4,605
0 votes
1 answer
10k 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....
user avatar
1 vote
4 answers
28k 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 ...
user avatar
  • 3,482
2 votes
1 answer
109 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 ...
user avatar
  • 3,135
20 votes
2 answers
809 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 ...
user avatar
9 votes
2 answers
69k views

SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

I am trying to query two tables and get results like the following: Section Names shoes AccountName1, AccountName2, AccountName3 books AccountName1 The tables are: CREATE TABLE dbo....
user avatar
0 votes
2 answers
4k views

Concatenate column values [duplicate]

I have a query that uses a couple joins to get a name from another table. It works okay except that I would like to combine "Examiners Name" instead of having multiple duplicates: ID | Name ...
user avatar
34 votes
2 answers
132k views

'CONCAT' is not a recognized built-in function name

A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however: 'CONCAT' is not a recognized built-in function name. ...
user avatar
  • 1,221