Substing data

We are trying to pull the number 1 from the below string.  To be sure its the number 1 located after the third LIT~(  
This value will always be after the third LIT~( string.  We were trying to use the charindex but couldn't get it to work.


Thanks we are using sql 2000.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

I would guess you need the number from parentheses after the third occurrence of the '~LIT~(' string not the number 1.

To avoid long complex expression you can create following function:
CREATE FUNCTION dbo.GetNrAfterLit3rd(@inpstr AS varchar(200))
RETURNS varchar(200)
  DECLARE @retval AS varchar(200), @substr1 AS varchar(200), @substr2 AS varchar(200)
  SET @retval = ''
  SET @inpstr = SUBSTRING(@inpstr, CHARINDEX('~LIT~(', @inpstr), 200)
  SET @substr1 = STUFF(SUBSTRING(@inpstr, 7, 200), CHARINDEX('~LIT~(', SUBSTRING(@inpstr, 7, 200)), 6, '~XXX~(')
  SET @substr2 = SUBSTRING(@substr1, CHARINDEX('~LIT~(', @substr1)+6, 200)
  SET @retval = SUBSTRING(@substr2, 1, CHARINDEX(')', @substr2)-1)
  RETURN @retval

Open in new window

Calling sample:
DECLARE @str AS varchar(100)
SET @str = '~LIT~(sp00000009),~LIT~(),~LIT~(0123),~LIT~(195)'
SELECT dbo.GetNrAfterLit3rd(@str)

Tested on SQL 2008 but it should work on SQL 2000. The constant 200 repeated several times in the code means the maximum input string length. I am just not sure about the SUBSTRING() function behavior in SQL 2000 when the third parameter exceeds the actual string length.

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
stevendeveloperAuthor Commented:
Thank you very much for the help works excellent!
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

From novice to tech pro — start learning today.