get only the alphabets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

get only the alphabets

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-12 : 16:59:29

i have productname column in my table and the query i need to get only the alphabets.

column contains the special characters(,.-@/), numbers(0-9), space

sample data

Foodhold USA,mlc.
Beverage Partners Worldwide (North canada)......
Bread World 8

my expected output will be

FoodholdUSAmlc
BeveragePartnersWorldwide(Northcanada)
BreadWorld


SELECT productname,  
SUBSTRING(Name,1,ISNULL(NULLIF(PATINDEX('%[^A-Za-z.''0-9]%',LTRIM(RTRIM(productname))),0)-1,LEN(productname))) AS noSpecials
 FROM Manufacturer

but it is not working. can anyone please show me sample query..

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-12 : 17:08:46

small addition,

i want to strip below special characters as well
!@#$%^&*():'"{}[]\|?-+=

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-12 : 17:11:31

small addition,

i want to strip below special characters as well
!@#$%^&*():'"{}[]\|?-+=

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-12 : 17:20:27

Here is an example of how you can do this. This filters in characters. With some minor changes you can make it filter out characters as well.

CREATE TABLE #tmp (n INT, x VARCHAR(64));
INSERT INTO #tmp VALUES (1,'Foodhold USA,mlc.'),(2,'Beverage Partners Worldwide (North canada)......')

-- characters you want to keep
DECLARE @valuesToKeep VARCHAR(128) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()';

-- if you plan to use it in production, replace the spt_values with a Numbers table.
SELECT
	a.n,
	b.Alphabets
FROM
	#tmp a
	CROSS APPLY 
	(
		SELECT
			SUBSTRING(x,number+1,1)
		FROM
			#tmp t
			INNER JOIN master.dbo.spt_values s ON
				s.number < LEN(t.x)
		WHERE
			s.type = 'P'
			AND @valuesToKeep COLLATE Latin1_General_CS_AS  LIKE 
				'%' + SUBSTRING(x,number+1,1) + '%' 
			AND t.n = a.n
		FOR XML PATH('')
	) b(Alphabets);
	
DROP TABLE #tmp;

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-13 : 01:51:13

USE DBTraining1
GO
IF OBJECT_ID('fn_GetAlphabets') Is Not Null
DROP FUNCTION dbo.fn_GetAlphabets
GO
CREATE FUNCTION fn_GetAlphabets(@input VARCHAR(20))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@input) > 0
SET @input = STUFF(@input,PATINDEX('%[^a-z]%',@input),1,'')
RETURN @input
END

SELECT * FROM TableName
WHERE dbo.fn_GetAlphabets(TableName.ProductName) = TableName.ProductName

veeranjaneyulu

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-13 : 07:26:37

Thanks James and veera for the best knowledge. Appreciate your time on this post

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources