• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

REPLACE or RTRIM to remove characters from a value

I have a column with a few thousand rows that all begin with some sort of name but then end with a '#' and then a 5 or 6 digit number.

my desired result is to extract only those numbers into another column.

examples:

Doe, John and Jane #123456
Royal blue restaurant, LLC #12345

0
RustyZ32
Asked:
RustyZ32
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
update urTable
set urNewColumn = RIGHT ('urColumnName', len('urColumnName')-charindex('#', 'urColumnName') )
where charindex('#', urColumnName ) > 0
0
 
David KrollCommented:
select substring(fieldname, charindex('#', fieldname', 0), len(fieldname))
0
 
lluddenCommented:
I use this function for stuff like that.

Then
SELECT dbo.Strpiece('Doe, John and Jane #123456','#',1)

CREATE FUNCTION [dbo].[StrPiece] (@String varchar(500), @Delimiter char, @Position int)  
RETURNS varchar(100) AS  
BEGIN
Declare @CurrentSection int
Declare @CurrentPosition int
Declare @SubString varchar(100)
Set @CurrentSection = 0
Set @CurrentPosition = 1
Set @Substring = ''
While @CurrentPosition <= Len(@String)
    Begin
      If Substring(@String,@CurrentPosition,1) = @Delimiter
            Set @CurrentSection = @CurrentSection + 1
      Else
             if @CurrentSection = @Position
                  Set @SubString = @Substring + Substring(@String, @CurrentPosition, 1)
      Set @CurrentPosition = @CurrentPosition + 1
    End

Return ( @Substring)
END
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now