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

LVL 6
RustyZ32Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.