Solved

Getting certain data from a string

Posted on 2016-11-08
1
59 Views
Last Modified: 2016-11-08
I have a string in the DB

"JRSJC2LPValueSummary(122012)pdf(1179185).pdf"  

I need to get the number between the ().  If there is more than one set of (), like in this example, then I need the number from the 2nd set of ().

1179185

I can get the first set by using this:

substring([FileName], charindex('(', [FileName]) + 1, charindex(')', [FileName]) - charindex('(', [FileName]) - 1) AS DocumentNumberParsed

I don't know how to get the 2nd set IF there is a 2nd set.

I appreciate any help.
0
Comment
Question by:huerita37
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41879577
SELECT [FileName], SUBSTRING([FileName], FileName_Lparen_Final + 1,
    CHARINDEX(')', STUFF([FileName], 1, FileName_Lparen_Final + 1, ''))) AS FileNameNumber
FROM (
    VALUES('JRSJC2LPValueSummary(122012)pdf(1179185).pdf'),
                  ('JRSJC2LPValueSummary(122012).pdf')
) AS test_data([FileName])
CROSS APPLY (
    SELECT CHARINDEX('(', [FileName]) AS FileName_Lparen1
) AS ca1
CROSS APPLY (
    SELECT CHARINDEX('(', [FileName], FileName_Lparen1 + 1) AS FileName_Lparen2
) AS ca2
CROSS APPLY (
    SELECT CASE WHEN FileName_Lparen2 > 0 THEN FileName_Lparen2 ELSE FileName_Lparen1 END AS FileName_Lparen_Final
) AS ca3
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question