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.
47
questions
1
vote
3answers
70 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
68 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
21 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, -- ...
1
vote
4answers
267 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
26 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> </div>
<div>[flv:http://submarino-libre....
0
votes
2answers
23 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 ...
3
votes
1answer
637 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
2answers
58 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)' ...
4
votes
2answers
167 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
15 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
133 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
551 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
31 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
69 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
51 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
175 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
112 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
71 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
97 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
207 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
244 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
270 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
395 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
23 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
261 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
1k 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,...
14
votes
1answer
33k 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
3k 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
2k 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
391 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
7k 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)
&...
8
votes
1answer
7k 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
5k 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
53 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
5k 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
193 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
282 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\...
7
votes
3answers
2k views
Finding the most commonly used non-stop words in a column
Simply put, I have a Postgres column containing ordinary prose and would like to determine the x most commonly used words ("word" being a group of characters delimited by a space, but not being stop ...
10
votes
6answers
38k views
Trim whitespace (spaces, tabs, newlines)
I'm on SQL Server 2014 and I need to clean whitespace from start and end of a column's content, where whitespace could be simple spaces, tabs or newlines (both \n and \r\n); e.g.
' this content ...
4
votes
2answers
6k views
How to trim first digit off an integer value?
i have data like Column1, i want to convert the Column1 data as Column2.
Can any one suggest to me how i can achieve this in SQL?
Column 1 Column2
1000001 1
1000002 2
1000003 ...
2
votes
2answers
2k views
Locate a specific word in a database
Goal
Locate a specific word among tables in a database and display the name of the table and column in a list.
Problem
There are TOO many tables to locate a specific word in the database.
Info
...
7
votes
4answers
117k views
Replace space (“ ”) with no space (“”) in one column
I have a table like this:
ID | Propinsi | Kota |
_________________________
1 | Aceh | Denpasar
2 | Aceh | Banda Aceh
3 | Sumatera | Asahan
This table has many rows. The problem is I want ...
