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
0answers
9 views

How to join on a column that includes the ID but has additional strings (Redshift)

I have 2 tables with the following 2 columns that I would like to use as the key for my join: a column that has the ID of the call and I have another one which is the link to the call, containing the ...
0
votes
1answer
32 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 | ...
0
votes
1answer
28 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, ...
0
votes
2answers
60 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 ...
1
vote
1answer
114 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 ...
0
votes
0answers
25 views

How to separate a part of a string from other parts in Oracle SQL using regular_expression

I have a string like this : string := ' (sum(x) = 12 and sum(y) = 13 and sum(z) = 14 ) and w = 'p' AND q = 'p' AND l ...
3
votes
2answers
507 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 * ...
0
votes
0answers
305 views

String or binary data would be truncated, with an nvarchar(max)

I'm facing a well known error, but this time I cannot figure out the problem. I'm working with SQL 2014 and I need to replace some values inside an NVARCHAR(MAX) column. To do this I retrieve the ...
1
vote
3answers
77 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) = ...
3
votes
1answer
79 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 ...
0
votes
1answer
28 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, -- ...
2
votes
4answers
752 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 ...
0
votes
1answer
31 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 ...
1
vote
1answer
28 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....
0
votes
2answers
29 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 ...
5
votes
1answer
2k 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 := :...
0
votes
3answers
133 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)' ...
5
votes
2answers
387 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) ...
1
vote
1answer
18 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 ); $...
1
vote
1answer
413 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 ...
2
votes
2answers
2k 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 ...
3
votes
1answer
32 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'?
0
votes
1answer
139 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 ...
0
votes
1answer
84 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 ...
1
vote
2answers
241 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, ...
1
vote
2answers
303 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} ...
1
vote
2answers
89 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 ...
-1
votes
2answers
298 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 ...
0
votes
0answers
244 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 ...
2
votes
2answers
307 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 ...
1
vote
1answer
327 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 ...
0
votes
0answers
516 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 ...
0
votes
1answer
27 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 ...
-2
votes
2answers
365 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 '...
7
votes
1answer
2k views

Is the function PARSENAME() the opposite of QUOTENAME()

In the question Is there any (hidden) built-in function on MS-SQL to unquote object names? the author was seeking advice on if there was an internal function to "UNQUOTE" a quoted string. The author ...
1
vote
1answer
2k views

Use STUFF function to insert at multiple points in a string?

Is there a way that I can replace a substring in SQL server while remaining 'aware' of what I'm replacing so that I can then re-insert that content? I am building a string of column names to pivot on,...
18
votes
1answer
47k views

Decode Base64 String Natively in SQL Server

I have a varchar column in a table in SQL Server which hold a base64-encoded text string which I would like to decode into it's plain text equivalent Does SQL Server have any native functionality to ...
0
votes
1answer
4k views

Concatenate with NULL values ( Should not replace NULL )

After some time searching for an answer, I got a LOT of question saying to use coalesce,concat(), and etc, but what I need is: Table: create table nulos ( nome varchar(max)) alter table nulos add ...
6
votes
3answers
4k views

Filtering characters to just return numeric characters in a string?

I want to filter an nvarchar field to return just the numeric values. I have some SQL to do this but it seems way more complicated than it needs to be. I am interested in finding out if anyone has a ...
4
votes
2answers
434 views

Efficient way to see what pattern matches a single word

I'm creating a function to find out what search pattern would best fit a single string. The string to match will be compared against a table of what could be possibly hundreds of possible patterns to ...
4
votes
2answers
10k views

Find rows with similar string values

I have a Microsoft SQL Server 2012 database table with around 7 million crowd-sourced records, primarily containing a string name value with some related details. For nearly every record it seems ...
1
vote
0answers
51 views

Finding and replacing non-specific text to lowercase variant

I've been cracking my brain on a problem that I had at work. In one of my tables is a bunch of rows, with a certain column that contains XML data that has several properties like this: (simplified) &...
9
votes
1answer
10k views

STRING_SPLIT with a multiple-character separator?

SQL Server 2016 introduced STRING_SPLIT which is very fast and an excellent replacement for any homemade implementation people would roll before 2016. Unfortunately, STRING_SPLIT only supports a ...
3
votes
4answers
2k views

Compare words in a string without considering their positions?

In Postgres 9.6 I want to test whether two strings like these are considered the same: 'this is a test number 01', 'number this is 01 a test' So I have created this function: CREATE OR REPLACE ...
6
votes
2answers
7k views

Select a CSV string as multiple columns

I'm using SQL Server 2014 and I have a table with one column containing a CSV string: 110,200,310,130,null The output from the table looks like this: I want to select the second column as multiple ...
4
votes
2answers
2k views

Extract street address from string with multiple delimiters and positions

Trying to pull just the Street addresses out of this data: CREATE TABLE foo(Places varchar(50)); INSERT foo(Places) VALUES ('MARKET @123 NORTH RD'), ('HARDWARE @600 Main RD ;W1'), ('MAIN AV / NORTH ...
1
vote
1answer
54 views

Is there a better way than this to split a string and get the first substring?

I'm trying to cut off everything after a certain character (in this case '&') and if there is no occurence of that character, get the entire string. select left(@str,isnull(nullif(charindex('&...
2
votes
2answers
6k views

Parse a string to find data between delimiters, multiple occurrences

I have a SQL Server table with one specific nvarchar column (let's assume it's called details) having data like below: Item1: A100 ; Item2: B200; Item3:C300: Item4:D400; Item5:E500; Item6: F600600600;...
1
vote
1answer
200 views

nested if expressions for string in excel source file in derived column expression [closed]

I have a column named Product which contains strings. Everything starting with "ZYT" should be shown: "ZYTOL" and "AZ" --> "AZAB" "UXO" --> "UXOL" "ADX" --> "ADXIZN" How could I write the ...
2
votes
1answer
291 views

Extract Database Name From String [duplicate]

In a database we have the full file path for a database field type is nvarchar(255) and structure/data would look like this: M:\Blue\Red\Green\Folder\Pink\Purple\Database1.accdb M:\Blue\Red\Orange\...