Remove the numbers from a field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Remove the numbers from a field?

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2013-08-05 : 17:41:48

Hello is it possible to exclude the numbers in a field? I have a column named sku that has different sku's. Some are like this AART553 and some are like this DT9. I would like to exclude the numbers and only return the letters in the sku column. How would I go about doing this? I would like the return value to look like this AART and DT.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 17:49:08

Brute force approach:

SELECT
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		sku,'1',''),'2',''),'3',''),'4',''),'5',''),
		'6,',''),'7',''),'8',''),'9',''),'0','')
FROM
	YourTable;

taunt
Posting Yak Master

128 Posts

Posted - 2013-08-05 : 18:03:24

quote:

Originally posted by James K

Brute force approach:

SELECT
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		sku,'1',''),'2',''),'3',''),'4',''),'5',''),
		'6,',''),'7',''),'8',''),'9',''),'0','')
FROM
	YourTable;

That worked, how would I go about doing that and using distinct? I tried this:

SELECT distinct REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
sku,'1',''),'2',''),'3',''),'4',''),'5',''),
'6,',''),'7',''),'8',''),'9',''),'0','')

But it will ignore anything with six in it. WDHV, WDHV6, WDHV66. IS my result

taunt
Posting Yak Master

128 Posts

Posted - 2013-08-05 : 18:20:42

I figured out the 6 thing. It was '6,','') should have been '6',''). Now works fine.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 19:48:09

quote:
Originally posted by taunt

I figured out the 6 thing. It was '6,','') should have been '6',''). Now works fine.

Thanks

I plead guilty. Sorry about that. But, glad you figured it out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 04:14:13

[code]DECLARE @Sample TABLE
(
Data VARCHAR(100) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('Art123'),
('DS9'),
('SwePeso');

SELECT Data,
CASE PATINDEX('%[0-9]%', Data)
WHEN 0 THEN Data
ELSE SUBSTRING(Data, 1, PATINDEX('%[0-9]%', Data) - 1)
END
FROM @Sample;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

taunt
Posting Yak Master

128 Posts

Posted - 2013-08-06 : 11:13:23

quote:
Originally posted by James K

quote:
Originally posted by taunt

I figured out the 6 thing. It was '6,','') should have been '6',''). Now works fine.

Thanks
I plead guilty. Sorry about that. But, glad you figured it out.

NP just a slight miss type. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources