Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [substring]

The SUBSTRING function is used to return a portion of a string.

Typical implementations of SUBSTRING allow the user to specify the starting position, and length of the result string to be returned from the source string, or to return the left-most or right-most "X" number of characters from the source string.

62 questions
13
votes
1 answer

Changes to estimates on predicates that contain SUBSTRING() in SQL Server 2016?

Is there any documentation or research about changes in SQL Server 2016 to how cardinality is estimated for predicates containing SUBSTRING() or other string functions? The reason I'm asking is that I was looking at a query whose performance…
James Lupolt
  • 4,278
  • 5
  • 31
  • 46
9
votes
1 answer

Why do string functions on SQL based database servers start at position 1 instead of 0?

This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the following query would be used to select the first…
VKK
  • 203
  • 1
  • 2
  • 4
8
votes
3 answers

Is there a SQL Server implementation of the Longest Common Substring problem?

Is there a SQL Server implementation of the Longest Common Substring problem? A solution that checks with all rows of a column in SQL Server? I have seen solutions that take two strings as input, but no SQL Server solution that looks at all rows of…
ManOnAMission
  • 459
  • 4
  • 16
7
votes
2 answers

How do you count the occurrences of an anchored string using PostgreSQL?

If I have a string in a column on a row in a table like this 1 2 2 2 2 2 2 How would I count the occurrence of a substring 2 inside the string. Assume nothing other than a space-delimiter of " ". For the purposes of this, let's treat the numbers as…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
5
votes
2 answers

At finding a substring, find the end position as well

A LIKE clause can test if a string occurs in another and the CHARINDEX function can give the start position of the first match. In my case, I'm interested in the end position though, which is, due to the intricacies of collations, not derivable from…
John
  • 775
  • 3
  • 17
5
votes
1 answer

MSSQL: Generate a 8 digit serial/Barcode number from a prefix number

I have a complicated task ahead of me and I really could use some of your brains to help me out. At my work we have a database where we store barcode digits. The table contains a 6 digit as a prefix for an 8 digit barcode, were we currently manually…
pancake
  • 177
  • 1
  • 6
5
votes
2 answers

Substring without the first n characters

I'm developing a SQL Server 2016 stored procedure and I want to get the last characters of a varchar(38) column. I know there will always be at least 18 characters and I don't know the exact length of the column, because it is variable. I think I…
VansFannel
  • 1,873
  • 5
  • 23
  • 36
4
votes
2 answers

Is this substring_index behaviour documented or a bug?

mysql> show variables like 'version'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | version | 5.5.38-0ubuntu0.12.04.1…
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
4
votes
2 answers

SQL join on an edited field

I'm trying to join Table1 and Table2 on the Url fields. However all the Urls in Table2 end with a "/" as per the example below: Table1 ╔═════════════════════╗ ║ Url1 ║ ╠═════════════════════╣ ║ http://site1.com ║ ║ http://site2.com…
neptr
  • 43
  • 4
4
votes
2 answers

Extract filename without extension from the absolute location

I'm trying to get a filename contained in the value of a specific column of my table. My table looks like this: absolutel_path \\Path\filename.extension I need to extract the filename (in the above filename) from the absolute_path…
Tyler Darden
  • 63
  • 1
  • 1
  • 4
3
votes
1 answer

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. The version of MySQL is 8.0.19 on a Windows…
nicodemus
  • 33
  • 3
3
votes
3 answers

Is there a more concise way in Postgres to extract a portion of a string?

I'm using Postgres 9.5. I have a table with a column that records URLs. Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I came up with: select substring(url, 0, case…
Dave
  • 753
  • 10
  • 22
  • 39
3
votes
2 answers

Trying to check if a string contains a number exclusively

I have been trying to write a function to check if a string contains a number without that number being part of a bigger number (in other words if the number being searched for is '6' and the string is '7+16+2' it should return false, because the…
MrVimes
  • 629
  • 2
  • 11
  • 17
3
votes
2 answers

Remove substring from one column and put it on other column

I have a table like this: CREATE TABLE `mytable` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `column1` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `column2` varchar(255) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) )…
MagisterMundus
  • 185
  • 1
  • 5
3
votes
2 answers

How to separate name string by the spaces in the name in T-SQL

I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr I need to split those out into the four component parts of the name (if the name has four parts) to put each of them in their own column (last name, first name, middle…
marky
  • 129
  • 1
  • 1
  • 6
1
2 3 4 5