I have a nvarchar string from which I need to extract certain text from between characters.

Example: 1.abc.5,m001-1-Exit,822-FName-18001233321--2021-09-23 13:53:10 Thursday-m001-1-Exit-Swipe,Card NO: 822User ID: FNameName: 18001233321Dept: Read Date: 2021-09-23 13:53:10 ThursdayAddr: m001-1-ExitStatus: Swipe,07580ec2000002a52E917D0000000000372BA56E11010000

What I need:

| Name     | Phone Number   |    
| -------- | -------------- |    
| FName    | 1800123321     |

My Attempt:

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('-', col)))   1, LEN(col) - LEN(LEFT(col,      CHARINDEX ('-', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('-', col))) - 1);

CodePudding user response：

One way:

• Use patindex to find "FName-"
• Remove the start of the string up until and including "FName-"
• Use patindex to find "--"
• Remove the rest of the string from and including "--"

You can consolidate the query down to one line, but you'll find yourself repeating parts of the logic - which I like to avoid. And calculating one thing at a time makes it easier to debug.

select
  A.Col
  , B.StringStart
  , C.NewString
  , patindex('%--%',C.NewString) NewStringEnd
  , substring(C.NewString,1,patindex('%--%',C.NewString)-1) -- <- Required Result
from (
  values
  (N'1.abc.5,m001-1-Exit,822-FName-18001233321--2021-09-23 13:53:10 Thursday-m001-1-Exit-Swipe,Card NO:    822User ID:     FNameName:  18001233321Dept:    Read Date:  2021-09-23 13:53:10 ThursdayAddr:   m001-1-ExitStatus:  Swipe,07580ec2000002a52E917D0000000000372BA56E11010000')
) A (Col)
cross apply (
  values
  (patindex('%FName-%',Col))
) B (StringStart)
cross apply (
  values
  (substring(A.Col,B.StringStart 6,len(A.Col)-B.StringStart-6))
) C (NewString);