Remove both Special Characters and String Characters from output SQL Server

Question

I am looking to remove some string characters in an output that I have so that it only shows numbers rather then letters and numbers. My Query is:

Select DISTINCT t.processed 'Title',
(Select max(reconst) from isbn where bib# = t.bib#) 'ISBN',
t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc

The output from the Query is:

The desired output is in the ISBN column which would would look like 9781501132957, which would be removing the letters C and also the words such as hardcover, HRD1, acid-free paper, softcover,PAP, DVD, CD, pbk and special characters such as (), :, ., -

Thank you in advance

Answer 1

Try this code that uses a combination of SUBSTRING, PATINDEX, LEN, and REVERSE, to account for the possibility of 10-13 digit ISBN variability to return the exact ISBN with no trailing extras:

declare @myvar varchar(50)='C9781501132957 : (hardcover)'
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar)))

...or perhaps differentiating between other numbers in the string from ones that are at least 10 digits in size, like maybe a date of 1987 included in the description (if this is even needed):

declare @myvar varchar(50)='C9781501132957 : (hardcover) 1987'    
SELECT substring(@myvar,2,LEN(@myvar)-   PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',reverse(@myvar)))

i.e.

Select DISTINCT t.processed 'Title',
substring((Select max(reconst) from isbn where bib# = t.bib#),2,LEN(@myvar)-   PATINDEX('%[0-9]%',reverse(@myvar))) 'ISBN',    t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc

Answer 2

Use this simple function

Create Function [dbo].[fn_rm_special_char](@ver VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^0-9]%'
    While PatIndex(@KeepValues, @ver) > 0
        Set @ver = Stuff(@ver, PatIndex(@KeepValues, @ver), 1, '')

    Return @ver
End

Thanks