Questions tagged [string-manipulation]

String manipulation on the database server side is achieved using built-in string processing functionality (operators, functions). Use this tag for questions where string processing is central to the problem.

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

Replace specific string with blank even if partial match with another column value

I have a scenario to replace entire string from a set of comma delimited strings, if partial word matches with another column. I found very complex to explain in words and hence I have explained ...
Shiva's user avatar
  • 787
0 votes
1 answer
721 views

How To turn a string with "pipe-separated" values into individual rows in Oracle PL/SQL

I have a table with below structure : create table student_info ( item_number number, st_firstname varchar2(50), st_lastname varchar2(50), st_score varchar2(50) ) Here is a ...
Pantea's user avatar
  • 1,412
1 vote
2 answers
1k views

Replace every character in each word, except the first two characters

Due to privacy concerns, I need to hide personal information, while remaining somewhat understandable. I propose to achieve that by hiding everything except the first two characters. The word length ...
oren's user avatar
  • 13
1 vote
1 answer
281 views

Parse large string into multiple columns/rows

I have a string column that I need to parse into multiple different columns and rows. Sample string value: If the total charge exceeds {$10,000.00} ,pricing is recalculated to be a {50.00}% discount ...
Doc's user avatar
  • 23
3 votes
1 answer
205 views

String Manipulation of the Result from Recursive CTE

Good afternoon everyone I found just one post here within the last year about this, but it doesn't help my situation. I have been working with MySQL and trying to improve my knowledge of recursive CTE....
nicodemus's user avatar
2 votes
2 answers
922 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
3k views

Find and show special character codes from nvarchar column

I want to know which special characters (line breaks, tabs, etc) are in my column of type nvarchar. I know how I can select which rows contain a specific character. e.g. SELECT * FROM your_table WHERE ...
Adam's user avatar
  • 245
1 vote
2 answers
113 views

Remove third ordinate from string of 3 ordinates

I have geometries represented as strings (Oracle 18c): with cte as ( select 'LINESTRING ( 1.0 2.0, 3 4)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0)'...
User1974's user avatar
  • 1,457
0 votes
1 answer
524 views

Compare occurence of chars in PostgreSQL string

I have some data like this: metaphone lag FLKSW [null] PPS FLKSW PPS PPS PSP PPS And I want to compare the string values in both columns on the folowing condition: they're similar (assign some ...
willsbit's user avatar
1 vote
1 answer
73 views

In a string, replace third number in each set with new number

I have a string that has coordinates. Individual coordinates are separated by spaces. Vertices (X Y Z coordinates) are separated by commas. Vertex groups are wrapped in brackets and separated by ...
User1974's user avatar
  • 1,457
1 vote
1 answer
896 views

SQL in Access to add leading 0 in front of zip codes

for a project I am working with a table of zip-codes that I imported from a *.csv file, exported form Excel. Those zipcodes all have 5 numbers and are formatted as text within my database. Some of ...
Titan's user avatar
  • 11
-4 votes
1 answer
217 views

Select INTs from start of string separated by a character (pipe). Update other columns using these INTs

In the above screen shot, the Description column has many special characters. We want before | number update in T1 column and | after number in T2 column. Please suggest the proper syntax. If there ...
karthik sanapala's user avatar
1 vote
2 answers
528 views

Determine Ideal Collation Set for correct data storage

Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following: My JSON columns (which in MariaDB are LONGTEXT) have an automatically set collation of utf8mb4_bin. ...
DevelJoe's user avatar
  • 163
1 vote
0 answers
431 views

Convert any string to url valid percent encoding in BigQuery

I am trying to convert any string with any set of special characters into a valid url of the format below. In Bigquery Example: /artwork-v2/-̴̕ι-̶͔͛n̴e̷p̸u̴̒n̵uś̵̥o̵̙̾rt̷͗um̶̹͐-20380 encodes to: /...
Keegan Ead's user avatar
0 votes
2 answers
2k views

Batch update to replace special characters in SQL Studio Management

I am trying to remove/correct some special characters in my database that show strange behaviour when exported. Specifically & and the apostrophe ’ I ran a query to find the values that contain ...
Geo_explorer's user avatar
2 votes
1 answer
418 views

How can I change string output in a hierarchy tree with PostgreSQL?

I have a table like this: ------------------------------------------------- | id | description | parent_id | cost -------------------------------------------------- | 1 | Radiology ...
tucomax's user avatar
  • 37
1 vote
3 answers
255 views

String concatenation and DATE/Number ordinals in one string?

How can I combine functions with strings to end up with a whole in one sentence? I mean something like this (expected result): Today is the 3rd day of the year, the 2nd week of the year and the 1st ...
WIIcky's user avatar
  • 11
0 votes
2 answers
5k views

How to remove specials characters, but replace spaces?

I have a string with spaces & special characters: ABC%$! DEF*& GHI I want to remove all the specials characters, but replace the spaces with an underscore: ABC_DEF_GHI I have: REGEXP_REPLACE(...
Landon Statis's user avatar
0 votes
1 answer
1k views

Stored procedure to change string to datetime in MySQL

This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my ...
E.Aigle's user avatar
  • 105
0 votes
1 answer
241 views

Problem using 'Regular expression' in order to split characters of a column when there is no delimiter between them

I have a table with below structure: create table TBL_TEST ( col_id NUMBER, col_name VARCHAR2(500) ) Some example data : col_id | col_name ----------------- 1 | aetnap 2 | ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
41 views

How to massively change string capitalization in a table?

I have a project where authors' names have been inserted in all capital letters (ex. JOHN KRAUS, LEONARD MILES, NAOMI DAWSON). Now I need these values in "normal" case (ex. John Kraus, ...
Alain's user avatar
  • 103
0 votes
2 answers
94 views

How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?

I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys ...
SteveO29's user avatar
1 vote
1 answer
2k views

Error "ORA-12726:unmatched bracket in regular expression" while trying to separate a part of a string from other part

I have a string like this : v_sgtring:'sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]' and I need to separate sum:[] from where:[] and assign them to different variables like ...
Pantea's user avatar
  • 1,412
3 votes
2 answers
3k views

Replace a sequential set of numbers with special character

I have a varchar(200) column that contains entries such as, ABC123124_A12312 ABC123_A1212 ABC123124_B12312 AC123124_AD12312 A12312_123 etc.. I want to replace a sequence of numbers with a single * ...
Levi's user avatar
  • 33
1 vote
3 answers
191 views

REPLICATE() return type for VARBINARY input

REPLICATE ( string_expression ,integer_expression ) As documentation says, it returns the same type as string_expression. I see that return type is always VARCHAR. declare @vb varbinary(10) = ...
Alsin's user avatar
  • 365
3 votes
1 answer
677 views

Concatenate a string value from table A to a text field in table B, in a scalable way

On a daily basis, I am adding values from the following table (table1): Id Name Value 1 'Name 1' 789 2 'Name 2' 789 3 'Name 3' 789 To the text field (Values) in the following ...
Scarabas's user avatar
0 votes
1 answer
569 views

Behavior of STUFF function for empty strings

When working on answering another question (on SO), I came across some unexpected (to me) behavior with the STUFF function. SELECT STUFF ( 'a', -- character expression 1, -- start 1, -- ...
Zack's user avatar
  • 183
2 votes
4 answers
4k views

remove leading and trailing quotes

I have a field that may start and end with quotes. I only want to remove the quotes if the string starts and ends with quotes. If quotes exists inside the string then I wish to retain them. I have ...
Round's user avatar
  • 123
0 votes
1 answer
506 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
1 vote
1 answer
40 views

Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text

I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this: Some code... <div>&nbsp;</div> <div>[flv:http://submarino-libre....
gallo2000sv's user avatar
0 votes
2 answers
111 views

Get matching part from conditional pattern

I'm having a table like this: |id|key |value | ------------------------ | 0|name |Homer | | 1|surname|Simpson | | 2|town |Springfield| I'm running this query: SELECT * FROM ...
sc911's user avatar
  • 103
5 votes
1 answer
7k views

How to pass variable to PL/pgSQL code from the command line?

I am running a psql script from the command line with variables something like: psql ...... -v now_utc=$NOW_UTC Then I want to use this in my script like: $$ DECLARE _now_date timestamp := :...
Łukasz Woźniczka's user avatar
0 votes
3 answers
2k views

How to trim a string by finding multiple words

I want to trim a string if it contains certain words or characters. This is just part of a big stored procedure. I want if v_selection_name contains any of those characters .eg: ' (RES)' or ' (R)' ...
Fury's user avatar
  • 115
6 votes
2 answers
2k views

Wordcount in a field (all and unique) - is there a more elegant/optimal way?

Answering this question, Given this table (constructed from the question): CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL); INSERT INTO wordcount (description) ...
Vérace's user avatar
  • 29.7k
1 vote
1 answer
45 views

pgsql Return Partial Text Field Ending In A Whole Word

I found this PHP code that returns a sub-string ending with a whole word (IE separated by a space): if ( strlen( $body ) < $maxlength ) return $body; $body = substr( $body , 0 , $maxlength ); $...
Ron Piggott's user avatar
1 vote
1 answer
1k views

Using SUBSTRING and RTRIM Together

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into ...
Daylon Hunt's user avatar
4 votes
5 answers
8k views

How to replace multiple parts of a string with data from multiple rows?

Here's a fiddle to show what I'm after. Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in ...
youcantryreachingme's user avatar
3 votes
1 answer
91 views

Unexpected behavior from ltrim in postgres

select ltrim('Test Thing', 'Test '); Expected: 'Thing' Actual Result: 'hing' Why does the 'T' in 'Test' match and trim the 'T' from 'Thing'?
jarbot's user avatar
  • 31
0 votes
1 answer
333 views

How to eliminate for loop in a nested aggregate problem

I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be ...
user9645's user avatar
  • 177
0 votes
1 answer
277 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
1 vote
2 answers
520 views

Strip longest common suffix and concat in Postgres

Given a table t: id | name ------------ 1 | abcfug 1 | deffug 1 | hijfug 2 | etc How can I do something like: select string_agg(strip_lcs(name), ', ') from t where id = 1 returning: abc, def, ...
Sam's user avatar
  • 129
1 vote
3 answers
2k views

How do You concatenate a value to an array that was retrieved through a query in sql?

I have a table named "mkvtable" that establishes a correspondence between individual strings and arrays of strings: word | mkvword ---------+----------------- hello | {world,friend} ...
M.Templeman's user avatar
1 vote
2 answers
456 views

SUBSTRING and IF statement

I have varchar column, its name is column1 and has records like 111232435-0 and 1123443530. The last digit is not always 0; it can be 0-9. Some of them are digits with (-), other are digits. I want ...
alish's user avatar
  • 37
-1 votes
2 answers
892 views

TSQL Query to Remove Dates in Multiple Formats from Text Values [closed]

I have a table with list of text values that contain dates in any format types. I need a TSQL query to remove the dates from each row in the table. The date samples are below. What is the best way ...
Kevin's user avatar
  • 533
0 votes
0 answers
342 views

nText and String manipulation. SQL SERVER

Parent question - Thanks to Paul White and Tom V for answer. I am making little modification's to Paul White's answer to suit my requirements. I would like to change the following to replace a ...
Ilak's user avatar
  • 1
2 votes
2 answers
405 views

String manipulation tools to populate a variable for two "functions"

Versions: SQL Server 2008 R2 - 2014 I'm using a server audit to find changes to logins on my SQL Server instances, and writing it to the ERRORLOG directory path. Unfortunately, not all the instances ...
SQL_Hacker's user avatar
1 vote
1 answer
736 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
0 votes
0 answers
900 views

MySQL version of split_to_array or split_to_table?

PostgreSQL supports two String Functions, that are incredibly useful regexp_split_to_array regexp_split_to_table Both of these functions essentially take two strings, (a) a pattern and (b) an input ...
Evan Carroll's user avatar
  • 62.7k
0 votes
1 answer
48 views

PostgreSQL: Function to reformat a string

Suppose I have a string such as: abcdefghi and I would like to reformat it as: abc def ghi (that is, I have added some formatting spaces to the string). Is there a PostgreSQL function which will ...
Manngo's user avatar
  • 2,809
-2 votes
2 answers
806 views

Error when using a query as a function parameter [closed]

I have followed this tutorial for creating a string splitting function. When I try to run the function from the article using a SELECT as the first parameter, I get the following error: select '...
hello B's user avatar
  • 97