Questions tagged [concat]
In formal language theory and computer programming, string concatenation is the operation of joining two character strings end-to-end
76
questions
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 "...
- 163
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 "...
- 1
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(...
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 ...
- 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:
+------...
- 103
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. ...
- 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 ...
- 147
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 ({...
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 :
...
- 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
...
- 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 ...
- 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
---...
- 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. ...
- 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 ...
- 151
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 ...
- 13
-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 ...
- 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 ...
- 23
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
...
- 101
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.
...
- 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 ...
- 181
-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 ...
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 || ', ''' || ...
- 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 ...
- 27
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 ...
- 165
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 ...
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 ...
- 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 ...
- 654
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 (...
- 103
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 ...
- 172k
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 ...
user162241
-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 (...
- 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 ...
- 391
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 ...
- 127
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 ...
- 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, ...
- 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 ...
- 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 ...
- 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'...
- 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]
,'|#|'::...
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 ...
- 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 ...
- 73
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=...
- 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, ....
- 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 ...
- 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 ...
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,...
- 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....
- 111
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
...
- 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 ...
- 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 ...
- 8,317