Remove Characters in a String in SQL

Try this:

--sample data you provided in comments
declare @tbl table(codes varchar(20))
insert into @tbl values
('CGY PST - 58277') , ('CGY RMC PST # 58083'), ('CGY DR # 7443'), ('CSI # 1304'), ('PO# 0568 , 0570'), ('CGY DR# 7446')

--actual query that you can apply to your table
select SUBSTRING(codes, PATINDEX('%[0-9]%', codes), len(codes)) from @tbl

The key point here is to use patindex, which searches for a pattern and returns index where such pattern occur. I specified %[0-9]% which means that we search for any digit - it will return first occurrence of a digit. Now- since this would be our starting point to substring, we pass it to such function. Third parameter of substring is length. Since we want the rest of a string, len function makes sure that we get that :)

Applying to your naming:

select SUBSTRING(u_manualdoc, PATINDEX('%[0-9]%', u_manualdoc), len(u_manualdoc)),
       cardcode,
       cardname
from ODLN

You should use string functions charindex,len and substring to get it. See the code below.

select SUBSTRING(u_manualdoc,CHARINDEX('#',u_manualdoc)+1,LEN(u_manualdoc)- CHARINDEX('#',u_manualdoc))

EDIT

In addition to the other answers, you can use this simple method:

select
    substring(
        u_manualdoc,
        len(u_manualdoc) - patindex('%[^0-9]%', reverse(u_manualdoc)) + 2,
        len(u_manualdoc)
    ),
    cardcode, cardname
from ODLN

In this example, patindex finds the first non-digit (as specified by ^[0-9]) from the right side of the string, and then uses that as the starting point of the substring.

This will work on all of your sample strings (including 'PO# 0568 , 0570 CGY DR# 7446').

Or use SQL Server Regex, which lets you use more powerful regular expressions within your queries.

TRY THIS

DECLARE @table TABLE(DirtyCol VARCHAR(100));
INSERT INTO @table
VALUES('AB ABCDE # 123'), ('ABCDE# 123'), ('AB: ABC# 123 AB: ABC# 123'), ('AB#'), ('AB # 1 000 000'), ('AB # 1`234`567'), ('AB # (9)(876)(543)');
WITH tally
     AS (
     SELECT TOP (100) N = ROW_NUMBER() OVER(ORDER BY @@spid)
     FROM sys.all_columns),
     data
     AS (
     SELECT DirtyCol,
            Col
     FROM @table
          CROSS APPLY
(
    SELECT
(
    SELECT C+''
    FROM
(
    SELECT N,
           SUBSTRING(DirtyCol, N, 1) C
    FROM tally
    WHERE N <= DATALENGTH(DirtyCol)
) [1]
    WHERE C BETWEEN '0' AND '9'
    ORDER BY N FOR XML PATH('')
)
) p(Col)
     WHERE p.Col IS NOT NULL)
     SELECT DirtyCol,
            CAST(Col AS INT) IntCol
     FROM data;