Parse Number from VarChar field?

I have a varchar field containing the following data: 'Filing Fee For Case #3423'

I need a way, within a SQL query, to pull ONLY "3423" from the varchar field.  How can I do this?
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.

Anthony PerkinsCommented:
Without knowing the details, we can only speculate:

Select RIGHT(YourVarcharColumn, 4) CaseNumber
From YourTableName
brassmonkeyboyAuthor Commented:
Well the number can be any length.  How about this:  I want to select everything AFTER the "#" sign...
Anthony PerkinsCommented:
If it is the only # and there is always one than:

Select SUBSTRING(YourVarcharColumn, CHARINDEX('%#',YourVarcharColumn) LEN(YourVarcharColumn)) CaseNumber
From YourTableName
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anthony PerkinsCommented:
Oops, typo, let's try that again:
Select SUBSTRING(YourVarcharColumn, CHARINDEX('%#',YourVarcharColumn), LEN(YourVarcharColumn)) CaseNumber
From YourTableName
Anthony PerkinsCommented:
And that is still not right (sorry for the multiple posts):
Select SUBSTRING(YourVarcharColumn, CHARINDEX('#',YourVarcharColumn) + 1, LEN(YourVarcharColumn)) CaseNumber
From YourTableName
Anthony PerkinsCommented:
The following solution will get the last # and return Null if there is none:
Select      CASE
            WHEN CHARINDEX('#', YourVarcharColumn) > 0 THEN  -- There is at lease one #
                  SUBSTRING(YourVarcharColumn, LEN(YourVarcharColumn) - CHARINDEX('#', REVERSE(YourVarcharColumn)) + 2, LEN(YourVarcharColumn))
            ELSE Null      -- There is no #
      END CaseNumber
From YourTableName
If the string will always begin with 'Filing Fee For Case #' and you want everything after that, this is a bit simpler:

 REPLACE(FieldName, 'Filing Fee For Case #', '')

 The other suggestions (searching for just the "#") are more flexible.

 If there could be spaces around the number and you don't want those, add RTRIM and LTRIM.  For example,

 RTRIM ( LTRIM (REPLACE(FieldName, 'Filing Fee For Case #', '')))


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
The following function will return the first numeric part of a string.

create function [dbo].udf_numericpart
      @imput varchar(100)
returns integer as
      return (convert(integer, substring(@imput, patindex('%[0-9]%', @imput), patindex('%[^0-9]%', substring(@imput + 'A', patindex('%[0-9]%', @imput + 'A'), 100))-1)))

-- you can call the function by:

      dbo.udf_numericpart(YourVarcharColumn) as CaseNumber
you can use this function
select dbo.cleanup('Filing Fee For Case #3423')

drop function dbo.cleanup
create function dbo.cleanup(@str varchar(100))
 varchar (100)

      declare @ptr as smallint
      set @ptr = len (@str)
      while @ptr >=1
            if (substring(@str, @ptr,  1)<>'.' and isnumeric(substring(@str, @ptr,  1)) = 0 )
                  set @str = ltrim(rtrim(replace(@str,substring(@str, @ptr,  1),'')))
                       set @ptr = len (@str)
                  set @ptr = @ptr - 1

      return @str

hi do you still need assistance
brassmonkeyboyAuthor Commented:
Sorry, my whole system went crazy and I've not been able to try these yet.  I finally got it back up and running last night and will try the suggestions this afternoon.

Sorry for dragging this out so long!

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
Microsoft SQL Server

From novice to tech pro — start learning today.