SQL Query - Parse String Help

I need help parsing a string with SQL Query.  I have the following type of data, and need to be able to parse out specific values after a specific character:

Example of raw data:

Emp# 89898 & 29393 - terminated
Employee# 99233
Emp# 00903, 299982, 1220 - contract

I just need to be able to pull the numbers.  So output be as followed:
89898 & 29393
99233
00903, 299982, 1220

I tried the following, but seems to pull everything after my pawn sign.

SELECT DEPT_ID, SUBSTRING(NOTE, CHARINDEX('#', NOTE)+1, LEN(NOTE))
FROM DEPARTMENT

So basically any comment is follow by a dash.  Would like to pull anything between # and dash sign.  I found another example, but this one will not pick it up if the field does not have both characters.

SUBSTRING(NOTE, CHARINDEX('#', NOTE, 1)+1
            , CASE WHEN (CHARINDEX('-', NOTE, 0) - CHARINDEX('#', NOTE, 0)) > 0
            THEN CHARINDEX('-', NOTE, 0) - CHARINDEX('#', NOTE, 0) - 1
            ELSE 0
            END)
holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

winheimCommented:
create this new function

CREATE FUNCTION [dbo].[GetValidChars] (@str NVARCHAR(4000),  @validChars As NVARCHAR(100))
 RETURNS NVARCHAR(4000)
 AS
BEGIN 
	
	DECLARE @Result As NVARCHAR(4000)
	DECLARE @i as int
        
    Set @Result = ''
	
   
    Set @i=0
    While @i<len(@str)
    BEGIN
      Set @i=@i+1

        If CHARINDEX(  Substring(@str, @i, 1),@validChars) <> 0 
		BEGIN
            Set @Result = @Result + Substring(@str, @i, 1)
        END 

    END 

    RETURN(@Result)

END 

Open in new window


and use it in this way

/*
* Test it
*/
SELECT dbo.GetValidChars('swreirtji&23254356' /*string you would like to check*/,'0123456789' /*valid Chars*/)

Open in new window

0
PortletPaulfreelancerCommented:
If wishing to use only a query without functions, cross apply is very handy as the results of cross apply are then available "as if a field" to the next cross applies and/or in the selection clause. Another handy tip when using charindex is to deliberately add the string you are searching for so that the result is never zero e.g.

CHARINDEX( ' -', Note + ' -', 0)

so if the actual field does not have that string in it, the result of the above = the length of that field.

Anyway, here's an alternative approach:
SELECT
      left(keep,pos1) AS [Pulled Numbers]
FROM yourtable AS t
CROSS APPLY (
              SELECT REPLACE(REPLACE(NOTE,'Employee# ',''),'Emp# ','')
            ) ca1 (keep)
	
CROSS APPLY (
              SELECT CHARINDEX(' -',keep + ' -', 0)
            ) ca2 (pos1)

Open in new window

Which of the samples given above produces:
PULLED NUMBERS
89898 & 29393
99233
00903, 299982, 1220

see this sqlfiddle
0
Scott PletcherSenior DBACommented:
SELECT
    DEPT_ID,
    NOTE,
    LTRIM(SUBSTRING(NOTE, CHARINDEX('#', NOTE) + 1, CHARINDEX('-', NOTE + '-') - CHARINDEX('#', NOTE) - 1)) AS [Pulled Numbers]
FROM DEPARTMENT
--...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

holemaniaAuthor Commented:
Thanks all, each example seems to work somewhat but not exactly what I was after.

Winheim your example only works, but if I set my lookup, it's only pulling numbers.

Example:  89898, 2223, 192898

The output now is 898982223192898.  I need to be able to pull that set of numbers after # and before - sign.

PortletPaul, I tried the cross join and it is working if the first cross join is exact.  Issue is that it can also vary from just Emp# or Employee#.  Sometimes it can be anything else, but what's consistent it that it's always follow by a # and then the employee ID followed by a dash - for comments.

Tried ScottPelcher's example and it will not work if it does not have a dash.  

To clarify the # is always there, but the dash is sometimes optional.  If they do not put in a comment, then it's not there.
0
Scott PletcherSenior DBACommented:
>> Tried [ScottPletcher's] example and it will not work if it does not have a dash.  <<

Hmm, I explicitly tested data w/o a dash before posting the code.

My code adds a dash if there isn't one: CHARINDEX('-', NOTE + '-').

[My code will NOT work if there isn't a "#", but then any output wouldn't make any sense without a "#".]

For example:


SELECT
    DEPT_ID,
    NOTE,
    LTRIM(SUBSTRING(NOTE, CHARINDEX('#', NOTE) + 1, CHARINDEX('-', NOTE + '-') - CHARINDEX('#', NOTE) - 1)) AS [Pulled Numbers]
FROM (
    SELECT 1 AS DEPT_ID, 'Emp# 89898 & 29393 - terminated' AS NOTE UNION ALL
    SELECT 1, 'Employee# 99233' UNION ALL
    SELECT 1, 'Emp# 00903, 299982, 1220 - contract' UNION ALL
    SELECT 1, 'Employ# 1234 -' UNION ALL    
    SELECT 1, 'Employ# 1234-'
) AS DEPARTMENT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
>>PortletPaul, I tried the cross join
:) cross apply (not cross join)
really the cross apply approach is best used to minimize function calls so it's overkill here

& given the additional rules, I'd go with Scott's

which did/does add the dash - so I too don't understand that comment.
0
holemaniaAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.