Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Parse Number from VarChar field?

Posted on 2006-04-23
11
Medium Priority
?
446 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:brassmonkeyboy
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16520792
Without knowing the details, we can only speculate:

Select RIGHT(YourVarcharColumn, 4) CaseNumber
...
From YourTableName
0
 

Author Comment

by:brassmonkeyboy
ID: 16520890
Well the number can be any length.  How about this:  I want to select everything AFTER the "#" sign...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16521059
If it is the only # and there is always one than:

Select SUBSTRING(YourVarcharColumn, CHARINDEX('%#',YourVarcharColumn) LEN(YourVarcharColumn)) CaseNumber
...
From YourTableName
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16521062
Oops, typo, let's try that again:
Select SUBSTRING(YourVarcharColumn, CHARINDEX('%#',YourVarcharColumn), LEN(YourVarcharColumn)) CaseNumber
From YourTableName
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 16521077
And that is still not right (sorry for the multiple posts):
Select SUBSTRING(YourVarcharColumn, CHARINDEX('#',YourVarcharColumn) + 1, LEN(YourVarcharColumn)) CaseNumber
From YourTableName
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 16521096
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
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1000 total points
ID: 16521525
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 #', '')))


 James
0
 
LVL 9

Expert Comment

by:auke_t
ID: 16522865
The following function will return the first numeric part of a string.

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

-- you can call the function by:

Select
      dbo.udf_numericpart(YourVarcharColumn) as CaseNumber
From
      YourTableName
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16522996
you can use this function
=================
select dbo.cleanup('Filing Fee For Case #3423')


drop function dbo.cleanup
go
create function dbo.cleanup(@str varchar(100))
returns
 varchar (100)
 as
begin      

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

      return @str
end

go
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16576106
hi do you still need assistance
0
 

Author Comment

by:brassmonkeyboy
ID: 16578073
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!

David
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

571 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