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.

57 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,168
  • 4
  • 25
  • 43
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
7
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
  • 449
  • 4
  • 16
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
  • 4
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,693
  • 5
  • 19
  • 34
5
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…
NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
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
  • 37,076
  • 14
  • 93
  • 173
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
  • 3
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
  • 619
  • 1
  • 8
  • 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
  • 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
2
votes
1 answer

Replacing a specific string in oracle

I've a database value 'INS. Company Cancelled' where multiple values are separated by '.' I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled' I've tried below two queries to update the field my output is like…
Aravind
  • 193
  • 2
  • 7
2
votes
1 answer

How to Extract SubString between two positions

I have a text column (emailbody) in a table (notification) which keeps a record of the HTML that went out for a particular email. Somewhere inside that HTML file, there is a line of text that contains the following line: Delayed ${shipmentId} for…
daniel9x
  • 311
  • 1
  • 6
  • 16
2
votes
1 answer

MySQL "Out of sort memory" error on a simple GROUP_CONCAT query

I have a (what it seems to me) simple query which fails to run on a simple table, the error is: Out of sort memory, consider increasing server sort buffer size These are the SQL statements which I'm trying to run: CREATE TABLE `permissions` ( …
ekstrakt
  • 121
  • 1
  • 3
1
2 3 4