Database Administrators Stack Exchange
Database Administrators Stack Exchange

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
36
votes
2 answers

Decode Base64 String Natively in SQL Server

I have a varchar column in a table in SQL Server that holds a Base64-encoded text string, which I would like to decode into its plain text equivalent. Does SQL Server have any native functionality to handle this type of thing? Here is a sample…
GWR
  • 2,847
  • 9
  • 35
  • 42
15
votes
1 answer

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 single-character separator, which isn't always enough.…
Petter Brodin
  • 897
  • 2
  • 11
  • 18
14
votes
5 answers

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 ' should become 'this…
Giovanni Lovato
  • 465
  • 2
  • 5
  • 15
10
votes
4 answers

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 to replace the space before the text in column…
Uchsun
  • 359
  • 3
  • 5
  • 16
8
votes
3 answers

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 better way to filter out any non-numeric…
user802599
  • 463
  • 2
  • 7
  • 14
8
votes
3 answers

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 word) across all rows. I've found two solutions…
Potato
  • 81
  • 1
  • 3
7
votes
1 answer

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 McNets had noticed, that some internal functions…
John K. N.
  • 18,854
  • 14
  • 56
  • 117
6
votes
2 answers

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) VALUES ('What a great day'); INSERT INTO wordcount…
Vérace
  • 30,923
  • 9
  • 73
  • 85
6
votes
2 answers

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 columns, putting each item of the CSV string in a…
Moslem Hadi
  • 163
  • 1
  • 1
  • 6
5
votes
1 answer

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 := :now_utc; BEGIN -- do something END $$ But I got…
Łukasz Woźniczka
  • 151
  • 1
  • 3
5
votes
2 answers

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 there are a dozen similar typo records and I am…
kscott
  • 151
  • 1
  • 2
  • 6
5
votes
2 answers

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 RD'), ('500 NORTH RD @LIBRARY'), ('500 ANYSTREET ;…
unsigned1138
  • 65
  • 1
  • 5
4
votes
2 answers

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 * so that I can group the distinct non-numeric…
Levi
  • 43
  • 1
  • 5
4
votes
5 answers

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 curly braces, mixed in with any other valid text…
youcantryreachingme
  • 1,655
  • 3
  • 21
  • 36
4
votes
2 answers

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 see if it matches any of them. I've got a…
Andrew H.
  • 71
  • 4
1
2 3 4 5