Getting SQL View with no special characters, spaces and with

Question

The Base Table:

Someid   |         SomeName
_______________________________
    1    |    Name -,#
    2    |    Something else ,-'

Desired Output, preferably in a in view:

Alias for SomeName = SomeAlias

    SomeAlias
________________
     name
 somethingelse

So my question is how to create a view that removes all funky characters and spaces (Not numbers) and changes everything to lower case.

I do not want to alter the SomeName column or entries in any way.

I am using SQL Server 2014

EDIT:

For the sake of any pseudo Code:

Base table =

SomeTable

Output Table =

SomeView

How many types of non-alphanumeric characters do you expect. Is it limited to '-', '#', '-', and ''' or are there others? — HashPsi, Jul 28 '15 at 13:40
You could certainly investigate the `replace()` and `lower()` functions. If, as HashPsi asked, your number of undesired characters is unmanageably high, a UDF might be a better solution. — underscore_d, Jul 28 '15 at 13:40
There are quite a few resources online which help you with this. For starters take a look [**HERE**](http://stackoverflow.com/questions/31209164/query-to-search-an-alphanumeric-string-in-a-non-alphanumeric-column/31209294#31209294) — Radu Gheorghiu, Jul 28 '15 at 13:42
@underscore_d replace will work fine if the number of characters to remove is limited. Otherwise, it will be better to write a function based on PATINDEX. — HashPsi, Jul 28 '15 at 13:43
Well lets say an arbitrary amount of special characters. I am more than willing to specify them in the View Query. — Terrance00, Jul 28 '15 at 13:44
See: http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function — HashPsi, Jul 28 '15 at 13:44

score 2 · Accepted Answer · answered Jul 28 '15 at 13:48

2

You can create a function like below,

USE DBName; 
GO
Create Function [dbo].[RemoveNonAlphaNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

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

Return @Temp
End

You can use that function while creating a View,

USE DBName; 
GO
CREATE VIEW SomeView
AS
SELECT SomeID, [dbo].[RemoveNonAlphaNumericCharacters](SomeName) as SomeAlias
FROM SomeTable ; 
GO
-- Query the view
SELECT *
FROM SomeView;

answered Jul 28 '15 at 13:48

Jigar

216
1
8

Thank you for the effort. I will attempt this now! – Terrance00 Jul 28 '15 at 13:49
Bear in mind these sorts of UDFs can be slowww. There are some mentions of possible ways to minimise slowdown in the other thread linked by HashPsi. However, I've never needed to do this, so I've not benchmarked whether this or any other method is fastest. – underscore_d Jul 28 '15 at 13:50

score 1 · Answer 2 · answered Jul 28 '15 at 13:53

there is some way to so it like REPLACE string , make FUNCTION to replace string and we can use regular expression also

DECLARE @regex INT,@string varchar(100)
SET @string='india!@#$%^&*()_+<>?:"{}| indian'
SET @regex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)
WHILE @regex > 0
BEGIN
SET @string = STUFF(@string, @regex, 1, ' ' )
SET @regex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)
END
SELECT @string

Getting SQL View with no special characters, spaces and with

2 Answers2