Want to exclude numbers from display content

Question

I want to exclude numbers from my extracted data in SQL Server.

I have a table Student with a column address of text datatype.

I want to select the address without the numbers in address.

For example:

select address from student;

The table might have the data as TUCSON AZ 85705-7598 USA.

My result should be TUCSON AZ USA

The hyphen makes this more difficult than it seems. What if the student is from Fuquay-Varina, NC? I would suggest writing a user-defined function for this, and testing it on your data. — Gordon Linoff, Feb 03 '18 at 14:01
Check out (https://stackoverflow.com/questions/630472/removing-nonnumerical-data-out-of-a-number-sql) the function from Lukeh. Same solution as @gordon mentiond — Raj, Feb 03 '18 at 14:16
`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, Feb 03 '18 at 15:47

score 1 · Answer 1 · answered Feb 03 '18 at 14:16

Here is an in-line approach.

The CROSS APPLY will split/parse the string and then reconstruct it excluding any portion begining with a number

Edit - I should add that the subquery B1 can easily be migrated into a Table-Valued Function.

Example

Declare @YourTable table (addr varchar(250))
Insert into @YourTable values
('TUCSON AZ 85705-7598 USA'),
('TUCSON AZ 85705 USA'),
('TUCSON AZ USA')

Select A.addr
      ,NewAddress = B.S
 From  @YourTable A
 Cross Apply (
                Select S = Stuff((Select ' ' +RetVal 
                  From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(A.addr,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                       ) B1
                  Where RetVal Not Like '[0-9]%'
                  Order by RetSeq
                  For XML Path ('')),1,1,'') 
             ) B

Returns

addr                        NewAddress
TUCSON AZ 85705-7598 USA    TUCSON AZ USA
TUCSON AZ 85705 USA         TUCSON AZ USA
TUCSON AZ USA               TUCSON AZ USA

score 1 · Answer 2 · answered Feb 03 '18 at 14:26

Please try this solution...

SOLUTION

Declare @YourTable table (addr varchar(250))
Insert into @YourTable values
('TUCSON AZ 85705-7598 USA'),
('TUCSON AZ 85705 USA'),
('TUCSON AZ USA')

;WITH CTE AS
(
    SELECT *,  PATINDEX ( '%[0-9]%' , addr  ) sta
         ,  PATINDEX ( '%[a-zA-Z]%' , SUBSTRING (addr , PATINDEX ( '%[0-9]%' , addr  )  , 1000 ))
            chars
    FROM @YourTable 
)
SELECT addr oldaddr , CASE WHEN sta = 0 THEN addr else STUFF(addr,sta,chars-1,'') end addr
FROM CTE

OUTPUT

oldaddr                       addr
----------------------------- -------------------
TUCSON AZ 85705-7598 USA      TUCSON AZ USA
TUCSON AZ 85705 USA           TUCSON AZ USA
TUCSON AZ USA                 TUCSON AZ USA

(3 rows affected)

Want to exclude numbers from display content

2 Answers2