Skip to main content

Questions tagged [string]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
118 views

What is the idiomatic way to truncate a string down to 100 byte-pairs?

nvarchar(100) holds 100 byte-pairs, as documented here. LEFT([Your String], 100) truncates your string down to 100 characters as documented here. These are not the same. Foolishly, I have used LEFT([...
J. Mini's user avatar
  • 533
1 vote
1 answer
1k views

When does Oracle still treat empty strings as NULL?

I was looking for the difference between VARCHAR and VARCHAR2 and learned, among other things, that Oracle’s VARCHAR2 treats empty strings as NULL, or the other way round. Also, apparently I should be ...
Manngo's user avatar
  • 2,849
0 votes
2 answers
292 views

How to convert getdate() stored as varchar to date

I have stored 'getdate()' as text in one of my column in a table. (It's a complicated scenario ,how I saved it) Now when I select the field what I am getting is getdate() instead of the date . How can ...
Viz Krishna's user avatar
1 vote
0 answers
48 views

How does mysql_real_escape_string() work?

I have to send some string (in C) as a query to MySQL, so i used mysql_real_escape_string() to escape some characters like \0 or \n: #include <mysql/mysql.h> int loginQuery(char *Nickname, char *...
SempriGno's user avatar
2 votes
2 answers
1k views

How to use replace text function in a column using another table as a "lookup table" reference?

Here's some examples to show what I'm after. The source table (the one to have its string values changed): | string | |-----------------------| | abc${hello}123 | | def${yolo}321 ...
Tiago Stapenhorst's user avatar
1 vote
1 answer
862 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
  • 357
0 votes
1 answer
1k views

Cannot Insert it into in my table for Special characters when ANSI_WARNINGS is Off even with nvarchar

I want to insert a data row into my table as name: DSKWOR00 , but I get this error : String or binary data would be truncated. so I turned ANSI_WARNINGS OFF and it inserted the row but the result ...
مجتبی حکیمیان's user avatar
0 votes
1 answer
581 views

Covert from literal HEX value to INT MariaDB

I have a situation where i need to get the integer value from a literal hex. Example: "ff" equals 255 in decimal, but if i use the HEX("ff") function it returns 6666. Is it ...
Adelina Andreea trandafir's user avatar
5 votes
1 answer
192 views

Match .csv values as INTs. If one value from a group in the .csv matches one in another group, then merge strings

Here we have two sets of numbers. The problem is that I can't figure out how to get from the input to the output of numbers (DDL and DML below and also in the fiddle here). current_data 1,2,3 1,4 1,5,...
user3050153's user avatar
0 votes
2 answers
839 views

Indexing on a fixed-length string (10 characters - phone numbers) for a large number of entries (performance)?

I understand that a lot of similar questions have already been asked but I could not find a definitive answer for my question. Briefly, I have to authenticate users using a REST API, with their phone ...
gkm's user avatar
  • 9
0 votes
1 answer
1k views

Calculating Average Value of JSONB array in Postgres

I have a column called "value" in my "answers" table. | value | |---------| | [1,2] | | [1] | | [1,2,3] | The type of "value" is "jsonb". I want to get ...
user avatar
7 votes
1 answer
2k views

text column compares equal to where clause but does not select matching row

We are having trouble when querying a table in our production database. One text column will compare equal to a string we filter on in the where clause, but postgres will not select the row. (We are ...
Nate's user avatar
  • 73
0 votes
1 answer
463 views

What exactly is the datatype input into a DATETIME value in MySQL

I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right ...
E.Aigle's user avatar
  • 105
1 vote
2 answers
164 views

Replace specific part of a string

I've got a table with multiple columns, APGRPID, VIRTKEY, LFDNR and PARAMETERLIST. I want to change a part of some strings in the PARAMETERLIST column. In the PARAMETERLIST I want to replace the "...
S_Koenig's user avatar
0 votes
2 answers
723 views

convert date in string format to date datatype sql [duplicate]

I have values in a column named Date as a nvarachar data type in the form of mmddyy and want to convert the values to a date datatype in the form of yyyy-mm-dd. What sql colde can I use to convert the ...
HappyCoder123's user avatar
2 votes
2 answers
2k views

WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance

Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
Kirk Saunders's user avatar
0 votes
1 answer
157 views

Levenshtein function returning different result based on limit value

The Levenshtein function is not working as I would have expect. Is there anything that I minsunderstood? Here is the query: SELECT c0.id, c0.engine_type, c0.mpg, c0.kwh, c0.price, c0.make, c0.model, ...
ryanzidago's user avatar
1 vote
1 answer
3k views

How to write correct array string parameter for mysql query?

I'm trying to put up a correct string to pass as a parameter to a simple query. Correct query that returns records: SELECT id_s FROM group_of_id_s WHERE date IN ('2020-08-01','2020-08-01','2020-08-02',...
nobsvalo's user avatar
2 votes
1 answer
39 views

Performing SQL string manipulations on a single column

I recently came across a problem at an interview which I wasn't able to answer. The ask was to write some SQL fetch the first three number(s) after crossing two 2 decimals. For example: Input table: ...
Rachit Singh's user avatar
0 votes
1 answer
91 views

String index for a list of values using IN($1,$2): will a btree index work?

We have a query that needs to compare a value to a list of values in a where clause. The strings are known and discrete. Does IN use the = operator to compare values? Would a b-tree index work for ...
Jed Schneider's user avatar
1 vote
1 answer
516 views

Count numbers in scientific notation (E-notation)

I'm trying to check if a part number field has scientific notation values and came up with this simple solution: SELECT count(*) as overall, count(*) filter (where partnumber ilike '%E+%') as ...
Matias's user avatar
  • 141
0 votes
1 answer
845 views

Mongodb index keeps growing in size eventhough the collection has a TTL index

I work on a mongodb sharded cluster (3.0.2 i know it's very outdated, not my call), on which i have a sharded db and a sharded collection on which i have multiple indexes. One of the indexes is a TTL ...
MrRobot's user avatar
  • 101
0 votes
1 answer
198 views

How do I send an array of strings to PG from PHP without the former becoming confused?

I want to do this, but for more than one string: SELECT * FROM table WHERE label <> $1 That selects all records from table where the label column is different from a single string. It works. ...
user avatar
3 votes
1 answer
3k views

Is there some function in PostgreSQL to simply check if the input string is valid UTF-8 according to PG?

For several years now, I've been hunting this extremely quick-footed little bug which knows every hiding place in my metaphorical coding house. He lurks in the walls, sometimes takes months to show ...
Arlington's user avatar
3 votes
1 answer
4k views

character varying vs. text as function parameter types in PostgreSQL

Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples: Example 1: CREATE OR REPLACE ...
Radu Dumbrăveanu's user avatar
2 votes
1 answer
2k views

How to output string values in single quotes?

I'm trying to convert values stored in a json into its string representation. The #>> operator works for this purpose for numbers, e.g.: SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'...
tinlyx's user avatar
  • 3,620
0 votes
1 answer
110 views

How do you combine the input of three parameters into one?

I'm working on a query that will run via C# to SQL server. I'm trying to make the query flexible and provide parameters for all the inputs. Here is the query: SELECT Test_Matrix.tblTestMatrix....
jrdnoland's user avatar
2 votes
1 answer
356 views

Prefix scale for UTL_MATCH.JARO_WINKLER_SIMILARITY

This is a repeat of a previous question from several years ago that did not receive an answer. Oracle has a string matching package, UTL_MATCH, which contains the subprogram JARO_WINKLER_SIMILARITY. ...
Nick's user avatar
  • 21
3 votes
3 answers
4k views

Is there a more concise way in Postgres to extract a portion of a string?

I'm using Postgres 9.5. I have a table with a column that records URLs. Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I ...
Dave's user avatar
  • 723
3 votes
1 answer
867 views

reverse the characters of a string without changing the position of the numeric value in T-SQL

With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value. So for string: abc223de11 have edc223ba11
anna's user avatar
  • 41
0 votes
1 answer
295 views

SQL Server 2017 - Extracting source and target column names within a string containing column mappings

I have the following format string stored in text (could be any number of columns): col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;... I'm trying to come up with an ...
shawnyshawny's user avatar
0 votes
2 answers
68 views

How to chop off X number of characters across multiple records?

Hello all! I have a field that contains geographical locations in the city-state format: Boston, MA Los Angeles, CA Helena, MT ... The field spans for some 230 records. My task is to chop off the ...
Shams Observer's user avatar
1 vote
2 answers
928 views

SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause

I have a MySQL query: SELECT p.informationProvider as pGLN, p.productID, p.productStatus FROM tblproducts AS p WHERE -- informationProvider is of type varchar(14) p....
Julian's user avatar
  • 452
-2 votes
2 answers
771 views

How to convert a multi-row result set into a single-row text separated by commas

I'm using this query select Name, valueType from mytable where ID = 1 and getting this table: But I need to covert the result set into something like: 'idGasto int, noTicket string, fechaFact ...
E.Rawrdríguez.Ophanim's user avatar
4 votes
2 answers
2k views

String comparison without CASE statements?

Is there a shorter more terse way to do a string comparison in SQL Server without wrapping it in a CASE WHEN ... THEN 1 END? In PostgreSQL, I can do SELECT c, c='foo' FROM ( VALUES ('foo'),('bar') ) ...
Evan Carroll's user avatar
  • 63.9k
2 votes
1 answer
815 views

Is there any string compaction option for PostgreSQL?

I have a table like this: CREATE TABLE BLOG( CATEGORY CHAR(32) ); Category is a column, which stored enum values. For example, I have 1 millions of rows and 10 unique values of Category. Let'...
Max's user avatar
  • 123
13 votes
2 answers
20k 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
  • 389
0 votes
1 answer
582 views

MariaDB are bitwise operators defined on strings?

Is the behavior of BITAND, (bitwise and) defined in MariaDB over strings? SELECT '0011' & '0011', 'ff' & 'ff', 'yy' & 'YY', '5' & '5'; +-----------------+-------------+-------------+--...
Evan Carroll's user avatar
  • 63.9k
1 vote
1 answer
771 views

Does a Levenshtein distance involve some computation for each single row?

Let's say I have, in a SQL DB, a table with 2 columns: id integer, description text and 100,000 rows. The column description has always < 20 characters. If I want to: SELECT * FROM mytable WHERE ...
Basj's user avatar
  • 171
13 votes
4 answers
68k views

Find if any of the rows partially match a string

I want to see if a table contains any sub-string of a given string. Let's say I have a string somedomain.com In database I have: blabladomain.com testdomain.com domain.com I need to make a ...
Emanuel's user avatar
  • 263
3 votes
1 answer
676 views

Test if a string is a valid, unquoted identifier?

I'm just wondering if there is an established method for testing if a string can be used as an unquoted PostgreSQL identifier? (unquoted because almost any string can be a quoted identifier). I ask ...
tinlyx's user avatar
  • 3,620
0 votes
1 answer
40 views

Value of columns inOracle tables

I have many schemas in one oracle databse named SCHEMA1, SCHEMA2...SCHEMA999 I want to find specific value of column named "JN_OSUSER" which is string 'Ed.Spencer' How to find in every table, in ...
user146611's user avatar
0 votes
0 answers
49 views

Storing a lot of text / string data strategies - analysis and solution

I have a LOB application backed by SQL Server 2016. Over the past few weeks, theres been issues whereby the SQL Server start throwing timeout errors - which has led to a bit more digging into the ...
RemarkLima's user avatar
13 votes
2 answers
4k views

Is there an efficient way to see the cause for "String or binary data would be truncated"?

This is a follow-up on this question. It is also related to this feature request from Microsoft. However, many years have passed and several major releases reached the market since it was reported. ...
Alexei's user avatar
  • 1,271
5 votes
3 answers
4k views

Equivalent to PostgreSQL's Dollar-quoted String Constants for SQL Server

Is there an equivalent of PostgreSQL's Dollar-quoted String Constants, on SQL Server? I would like to enter HTML string literals that would potentially contain single or double quotes in them. ...
Julien's user avatar
  • 151
5 votes
2 answers
2k views

Optimizing a Query for splitting strings with a known size

I have a Column that has a . as a delimiter, it looks like so.... abc.efg.hij I want a query that turns this into three columns, Col1, Col2, and Col3. I am wondering what the fastest way to do this ...
jdmneon's user avatar
  • 153
1 vote
2 answers
469 views

SQL regex PostgreSQL

Let's consider a table like that: id | name 1 | This is a test sentence 2 | is a sentence TeST 3 | This a IS test sentence 4 | iS a tEst sentence tHis 5 | This a test sentence is How ...
Roots's user avatar
  • 15
8 votes
2 answers
15k views

varchar(MAX) text cuts off while going more than 8000 characters inside a procedure

I have a procedure in which i have declared a variable with datatype varchar(MAX), declare @str varchar(MAX); set @str='select * from Table1...' print (@str); exec (@str); but when the text ...
Priyank Pahuja's user avatar
2 votes
1 answer
1k views

How do you unescape "C-like backslash escape sequence" that come from a function?

This will unescape the \n\n sequence. SELECT E'\n\nFOO'; But if that same sequence comes from a function, what do you do. CREATE FUNCTION asdf() RETURNS text AS $$ SELECT '\n\nFOO'::text $$ ...
Evan Carroll's user avatar
  • 63.9k
6 votes
5 answers
3k views

PostgreSQL: how can I shorten a multi-word string to few words?

I'm using PostgreSQL 9.5.6. I have space separated multi-word strings that I need to shorten to say 3 words? I've looked at the documentation and in order to use the substring() functions I need to ...
S.Ro's user avatar
  • 95