Substring of Variable Length

substring of variable length

You can use CHARINDEX in combination with REVERSE function to find last occurrence of _, and you can use RIGHT to get the specified number of characters from the end of string.

SELECT RIGHT([String],CHARINDEX('_',REVERSE([String]),0)-1)

SQLFiddle DEMO

Variable length substring between two characters

How about just using substring()?

select replace(substring(option_field_2, 13, 999), ';', '')

Or, if you don't know how long the prefix is:

select replace(stuff(option_field_2, 1, charindex(':', option_field_2) + 1, ''), ';', '')

Here is a db<>fiddle.

How to extract sub string of variable length using Python

You should try with a regular expression:

import re

re.search('Model: (.*)', dpath).group(1)

SQL Server Substring variable length - numerals of varying length with no consistent pattern

Not so nice, but if you really need a one-liner:

SUBSTRING(SUBSTRING(Name, PATINDEX('%[0-9]%',Name),999),1,PATINDEX('%[^0-9]%',SUBSTRING(Name,PATINDEX('%[0-9]%',Name),999)+'x')-1)

Or:

SUBSTRING(Name,PATINDEX('%[0-9]%',Name),LEN(NAME)-PATINDEX('%[0-9]%',REVERSE(Name))-PATINDEX('%[0-9]%',Name)+2)

Finding substring of variable length in bash

s=time=1234
time_int=${s##*=}
echo "The content after the = in $s is $time_int"

This is a parameter expansion matching everything matching *= from the front of the variable -- thus, everything up to and including the last =.

If intending this to be non-greedy (that is, to remove only content up to the first = rather than the last =), use ${s#*=} -- a single # rather than two.

---

References:

• The bash-hackers page on parameter expansion
• BashFAQ #100 ("How do I do string manipulations in bash?")
• BashFAQ #73 ("How can I use parameter expansion? How can I get substrings? [...])
• BashSheet quick-reference, paramater expansion section

How do I match a substring of variable length?

As ughai rightfully wrote in his comment, it's not recommended to use anything other then columns in the on clause of a join.

The reason for that is that using functions prevents sql server for using any indexes on the columns that it might use without the functions.

Therefor, I would suggest adding another column to imp table that will hold the actual ReceiptId and be calculated during the import process itself.

I think the best way of extracting the ReceiptId from the ref column is using substring with patindex, as demonstrated in this fiddle:

SELECT ref,
       RTRIM(SUBSTRING(ref, PATINDEX('%A[0-9][0-9][0-9][0-9]%', ref), 6)) As ReceiptId 
FROM imp

Update
After the conversation with t-clausen-dk in the comments, I came up with this:

SELECT ref,
       CASE WHEN PATINDEX('%[ ]A[0-9][0-9][0-9][0-9][0-9| ]%', ref) > 0
       OR PATINDEX('A[0-9][0-9][0-9][0-9][0-9| ]%', ref) = 1 THEN
           SUBSTRING(ref, PATINDEX('%A[0-9][0-9][0-9][0-9][0-9| ]%', ref), 6) 
       ELSE
           NULL
       END As ReceiptId 
FROM imp

fiddle here

This will return null if there is no match,
when a match is a sub string that contains A followed by 4 or 5 digits, separated by spaces from the rest of the string, and can be found at the start, middle or end of the string.

variable length substring of a string with specific beginning and ending

Assume source data housed in column A, put criteria header "Ingredients" & "Table of Nutritional Information" in B1 and C1.

Then,

In B2, formula copied down :

=MID(LEFT($A2,FIND(C$1,$A2)-1),FIND(B$1,$A2)+LEN(B$1)+1,599)

How to extract a variable length substring using ksh

With pure bash's parameter expansion capability.

var="SID_LIST_ORADBPOC1LSN ="  ##Creating shell variable here.
temp1="${var##*_}"             ##Removing everything till _ ## for longest match here.
echo "${temp1/ =/}"            ##Substituting space = with null here.
ORADBPOC1LSN

I am printing value of temp1's parameter expansion, you could save this into variable too as per your need.

---

---

OR if you want to do it in a single awk or so then try:

echo "$var" | awk -F'_| ' '{print $3}'

Python Extracting a substring of variable length

Here's a way using regex. Starts capturing after the 6th character and up to the first instance of an opening parens:

>>> import re
>>> string = "abcdefghijklmno(adfa adfds("
>>> m = re.search(r'.{6}(.*?)\(', string)
>>> m.group(1)
'ghijklmno'

This regex will capture up to the first newline or opening parens, whichever it sees first. The ?: portion means it is a non-capturing group. We don't need to store that data... we're just using the group for the or ('|') operation:

r'.{6}(.*?)(?:\(|\n)'

---

Related Topics