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.
73 questions
0
votes
0
answers
66
views
Extracting a special text from a given text in sql server
I have a text like this :
1XXXXXXXX/ZZZZZMR-A2
.L/TY7R7
.R/DOCS HK1/P/IRQ/A11111111/IRQ/27AUG66/M/25SEP32/XXXXXXXX/ZZZZZ
.R/TKNE HK1 2222222222222/1
1QQQQQQ/WWWWWWMS-B4
.L/TY7HZ
.R/DOCS HK1/P/IRN/...
- 1,486
0
votes
3
answers
653
views
Remove spaces and replace characters with regexp_replace()
I wish to ...
... remove spaces
... delete apostrophes
... replace 'é' and 'è' with 'e'
I use the function regexp_replace().
For the moment, I can delete the spaces but poorly. Indeed, when the ...
- 125
0
votes
1
answer
340
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 ...
- 797
0
votes
1
answer
1k
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 ...
- 1,486
1
vote
2
answers
2k
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 ...
- 13
1
vote
1
answer
401
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 ...
- 23
3
votes
1
answer
318
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....
- 33
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 ...
1
vote
1
answer
4k
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 ...
- 245
1
vote
2
answers
137
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)'...
- 1,497
0
votes
1
answer
702
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 ...
- 3
1
vote
1
answer
85
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 ...
- 1,497
1
vote
1
answer
1k
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 ...
- 11
-4
votes
1
answer
241
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 ...
1
vote
2
answers
712
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. ...
- 163
1
vote
0
answers
542
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:
/...
- 11
0
votes
2
answers
3k
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 ...
- 13
2
votes
1
answer
440
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 ...
- 37
1
vote
3
answers
284
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 ...
- 11
1
vote
2
answers
7k
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(...
- 151
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 ...
- 105
0
votes
1
answer
283
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 | ...
- 1,486
0
votes
1
answer
49
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, ...
- 103
0
votes
2
answers
112
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 ...
- 25
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 ...
- 1,486
4
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 * ...
- 43
1
vote
3
answers
213
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) = ...
- 375
3
votes
1
answer
733
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 ...
- 31
0
votes
1
answer
710
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, -- ...
- 193
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 ...
- 123
0
votes
1
answer
601
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
1
vote
1
answer
89
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> </div>
<div>[flv:http://submarino-libre....
- 27
0
votes
2
answers
132
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 ...
- 103
5
votes
1
answer
8k
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 := :...
- 151
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)' ...
- 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) ...
- 30.6k
1
vote
1
answer
49
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 );
$...
- 187
1
vote
1
answer
2k
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 ...
- 189
4
votes
5
answers
9k
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 ...
- 1,645
3
votes
1
answer
104
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'?
- 31
0
votes
1
answer
345
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 ...
- 177
0
votes
1
answer
312
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 ...
- 13
1
vote
2
answers
567
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, ...
- 129
1
vote
3
answers
3k
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}
...
- 78
1
vote
2
answers
497
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 ...
- 37
-1
votes
2
answers
973
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 ...
- 533
0
votes
0
answers
351
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 ...
- 1
2
votes
2
answers
436
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 ...
- 511
1
vote
1
answer
807
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 ...
- 171
0
votes
0
answers
1k
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 ...
- 64.6k