Trying to extract number between 2 characters '|' MS SQL

extract string between two characters excel
excel extract text between two spaces
excel extract text between two commas
excel extract text between two slashes
excel extract text between multiple parentheses
excel extract text between two dashes
extract text after character excel
excel extract text between quotes

I have column and need to extract number between 2 pipes |, example data inside is AAA|12345678|#RRR. I need to get this number 12345678.

my code is:

SELECT SUBSTRING(column_name,CHARINDEX('|',column_name) + 1, CHARINDEX('|',column_name) - CHARINDEX('|',column_name) - 1)
FROM [name].[name].[table_name]

Using your own code:

SELECT SUBSTRING(column_name,CHARINDEX('|',column_name) + 1, 
CHARINDEX('|',column_name) - CHARINDEX('|',column_name) - 1)
FROM [name].[name].[table_name]

The second part of substring is not correct. It should be:

SELECT SUBSTRING(column_name,CHARINDEX('|',column_name) + 1, 
CHARINDEX('|',column_name, CHARINDEX('|',column_name)))
FROM [name].[name].[table_name]

The nested CHARINDEX will look for the position of the second pipe. and the SUBSTRING will start from the first pipe and continue to the second

How to Extract a String Between 2 Characters in R and SAS, the initial position and final position as found in Steps 1-2. I have column and need to extract number between 2 pipes |, example data inside is AAA|12345678|#RRR. I need to get this number 12345678. my code is: SELECT SUBSTRING(column_name,CHARINDEX('|',

Assuming the 2nd position, you can use a little XML or ParseName()

XML Example

Declare @YourTable table (ID int,column_name varchar(max))
Insert Into @YourTable values
(1,'AAA|12345678|#RRR')


Select ID
      ,SomeValue = Cast('<x>' + replace(column_name,'|','</x><x>')+'</x>' as xml).value('/x[2]','varchar(max)')
 From @YourTable

ParseName() Example

Select ID
      ,SomeValue = parsename(replace(column_name,'|','.'),2)
 From @YourTable

Both would Return

ID  SomeValue
1   12345678

How to extract first/last n characters from string in Excel?, How do I separate the first two letters in Excel? The tutorial explains the specificities of the Excel MID function and demonstrates its creative uses: Mid formula to get a substring between 2 delimiters, extract Nth word, pull a word containing a specific character, and more.

String extraction is generally tricky in SQL Server. But if you only have one numeric value and are looking for it, then the code isn't that bad:

select patindex('%[0-9]|%', str),
       substring(str, patindex('%|[0-9]%', str), patindex('%[0-9]|%', str) - patindex('%|[0-9]%', str) + 1)
from (values ('AAA|12345678|#RRR')) v(str) 

Trying to extract number between 2 characters '|' MS SQL, use the following formula, assuming your data is in cell A2 =mid(left(A2,find(">",​A2)-1),find("<",A2)+1,len(A2)). To avoid error message if your data is not  Select the range that you will extract text between specified marks, and click Kutools > Text > Extract Text. 2. In the popping up Extract Text dialog box, you need to: (1) Add * between the two specified marks that you will extract text between, and type them into the Text box.

I would use PARSENAME() :

select parsename(replace(str, '|', '.'), 2)
from ( values ('AAA|12345678|#RRR')
     ) v(str);

Extracting Data Between two Characters in Excel, The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl". To find out how many characters should be extracted, you subtract the position of the first digit from the total length of the string, and add one to the result because the first digit is also to be included: =RIGHT(B2, LEN(A2)-B2+1) Where A2 is the original string and B2 is the position of the first digit.

Excel formula: Extract text between parentheses, Find more at http://bit.ly/Computergaga ☆ Extract text between two The LEFT function is used Duration: 6:30 Posted: Apr 13, 2014 Re: Extract A String Between Two Characters Give this formula a try =TRIM(LEFT(RIGHT(SUBSTITUTE("-"&A1,"-",REPT(" ",300)),900),300)) The above formula returns numbers as text, if you need the number to be a real number, just add 0

Extract Text Between Two Characters in Excel, I would like to extract the number (7 in this case) from Sales Tax field. Sales Tax is a Picklist field. I got an error message when I tried to write a  I have a variable length string that will have 2 dashes in it, for example: "Pay Bill 12345- ABC Mart - Check Number 1234" . I want to extract the text between the 2 dashes.

Extract a number between two characters - Answers, How to use Substring functions in Excel to extract text from cell, get a substring before If you want to operate on the result as a number, then wrap your formula in the How to extract text between two instances of a character If you are curious to try this as well as many other useful features included with  Re: Extract A String Between Two Characters I am trying to extract all text between two # and the length will vary. I have tried the formulas above and just can't get it to work.

Comments
  • Please include the sql server version you are using (2017, 2016, 2015, 2012R2, 2012, etc). See also How to determine the version, edition, and update level of SQL Server and its components
  • What's wrong with your current code? What is your question, exactly?
  • Thank you so much! ParseName works great, simple and effective, txt a lot.
  • @DKCroat Happy to help. Just a word of caution with ParseName() ... any extra periods in the string return unexpected results.