1

How can I extract and show only the characters from a string on a column I am searching in SQL Server if the position of the characters varies on the string

Example input:

Mich%ael#
#Scott
Ran%dy
A#nder%son

Output:

%#
#
%
#%

I only able to think of a query like

select
    columnname
from 
    dbo.tablename with (noLock)
where 
    columnname like '%[%#]%'

but this would not strip and show only the characters I want. I looked at substring() function but this requires knowing the position of the character to be stripped.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dren
  • 319
  • 2
  • 14
  • Replace letters with empty string. – PM 77-1 Oct 26 '16 at 01:55
  • Use REPLACE and PATINDEX with [^a-zA-Z]. There's tons of examples. – S3S Oct 26 '16 at 02:05
  • @PM77-1 Is it not the replace function requires that I indicate the exact input string? REPLACE ( string_expression , string_pattern , string_replacement ) Would it work if the string expression vary can I use regular expression on replace function? – Dren Oct 26 '16 at 02:06
  • This [link](http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server) may help you – Maruli Oct 26 '16 at 02:35
  • Set [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is *not recommended* to use this everywhere - quite the contrary! – marc_s Oct 26 '16 at 04:56

1 Answers1

1

If you don't want or can't use a UDF, consider the following:

Declare @YourTable table (SomeField varchar(50))
Insert Into @YourTable values
('Mich%ael#'),
('#Scott'),
('Ran%dy'),
('A#nder%son')

Select A.*
      ,Stripped = max(B.Value)
 From  @YourTable A
 Cross Apply (
                Select Value=Stuff((Select '' + String 
                 From  (
                         Select String= Substring(a.b, v.number+1, 1) From (select A.SomeField b) a
                         Join master..spt_values v on v.number < len(a.b)
                         Where v.type = 'P'
                        ) A
                 Where String in ('%','#')      --<<<< This is Your Key Filter
                 For XML Path ('')),1,0,'') 
             ) B
 Group By SomeField

Returns

SomeField   Stripped
#Scott      #
A#nder%son  #%
Mich%ael#   %#
Ran%dy      %
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Do i need to have a temp table to insert the values for this to work? If my input values run into hundreds of thousands i think it would not be practical to explicitly declare them all. – Dren Oct 26 '16 at 02:46
  • @Dren Nope. That was just for demonstration. Remove the first 6 lines Change (at)YourTable to your actual table name, and SomeField to your field name – John Cappelletti Oct 26 '16 at 02:47
  • Thanks your solution is great. Just made a minor modifications to use regular expression on the where string (Where String like '%[-.%$#&\/:()]%') – Dren Oct 26 '16 at 03:03