[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS SQL Replace Function

Posted on 2013-05-21
11
Medium Priority
?
435 Views
Last Modified: 2013-05-22
Hello,

I'm keen to build a MS SQL function that replaces characters and special characters with a '-' by special characters I mean all non (a-z A-Z 0-9) characters including spaces. The function also needs to only introduce a single '-' where for instance there may be a ' & ' or ' - ' so -
'Ross - On - Wye' would become 'Ross-On-Wye' or Health & Beauty' would become Health-Beauty.

Anyone got any great ideas on how to do this, I'm sure there is already probably something out there..

Appreciate any help you can give me.

Many thanks
0
Comment
Question by:garethtnash
11 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39185381
what you are trying to look for a regular expression in SQL server, where as there is no inherent support for this in SQL Server,

You have to write a SQL CLR function either in VB or C# and call it from C# in order to do this.
Check this comprehensive article which describes on how to create such a function
]
http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
0
 

Author Comment

by:garethtnash
ID: 39185449
There's a learning curve.. Would there be a quicker way of doing it in VBScript?

Thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39185890
Is '&' the only exception that you want to convert to '-'? Or do you have any such other scenarios?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:nemws1
ID: 39185926
Maybe you can modify this.  My code that *removes* any non alpha-numeric chars:

USE tempdb
GO

DROP FUNCTION dbo.fixchars
GO

CREATE FUNCTION dbo.fixchars
(@instr VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @badcloc INT = PATINDEX('%[^a-zA-Z0-9]%', @instr);
	WHILE (@badcloc > 0)
	BEGIN
		SET @instr = SUBSTRING(@instr, 0, @badcloc) + SUBSTRING(@instr, @badcloc + 1, 1000)
		SET @badcloc = PATINDEX('%[^a-zA-Z0-9]%', @instr);
	END
	RETURN @instr;
END
GO

BEGIN TRANSACTION 

CREATE TABLE badstuff (mytext VARCHAR(100));

INSERT INTO badstuff
VALUES ('hey');

INSERT INTO badstuff
VALUES ('yo man');

INSERT INTO badstuff
VALUES ('hey- there ^&  -   man');

SELECT dbo.fixchars(mytext) as fixed_text
FROM badstuff

ROLLBACK

Open in new window


Outputs:

hey
yoman
heythereman

Open in new window

0
 
LVL 23

Expert Comment

by:nemws1
ID: 39185939
Here, try this:

CREATE FUNCTION dbo.fixchars
(@instr VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @badcloc INT = PATINDEX('%[^a-zA-Z0-9-]%', @instr);
	WHILE (@badcloc > 0)
	BEGIN
		SET @instr = SUBSTRING(@instr, 0, @badcloc) + SUBSTRING(@instr, @badcloc + 1, 1000)
		SET @badcloc = PATINDEX('%[^a-zA-Z0-9-]%', @instr);
	END
	
	SET @badcloc = PATINDEX('%--%', @instr);
	WHILE (@badcloc > 0)
	BEGIN
		SET @instr = SUBSTRING(@instr, 0, @badcloc) + SUBSTRING(@instr, @badcloc + 1, 1000)
		SET @badcloc = PATINDEX('%--%', @instr);
	END
	
	RETURN @instr;
END
GO

Open in new window

0
 

Author Comment

by:garethtnash
ID: 39187371
Hello All,

Thanks for everything so far..

I've tried your suggestions, and characters have been removed, however no '-' have been added?

Ideally I was hoping that

yoman
heythereman

Would become -

yo-man
hey-there-man

So the function replaces all spaces and non a-z 0-9 characters with a - (hyphen / minus)

Grateful for your input.. thank you
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39187398
Did you try my latest function from the last post I made. Should be doing exactly that.
0
 

Author Comment

by:garethtnash
ID: 39187409
Hi Nemws1,

Yes, thats the function I just tested. removed all spaces also.. :(

I'll try it again
0
 

Author Comment

by:garethtnash
ID: 39187454
Umm yes so --

CREATE FUNCTION [dbo].[fixchars]
(@instr VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @badcloc INT = PATINDEX('%[^a-zA-Z0-9-]%', @instr);
	WHILE (@badcloc > 0)
	BEGIN
		SET @instr = SUBSTRING(@instr, 0, @badcloc) + SUBSTRING(@instr, @badcloc + 1, 1000)
		SET @badcloc = PATINDEX('%[^a-zA-Z0-9-]%', @instr);
	END
	
	SET @badcloc = PATINDEX('%--%', @instr);
	WHILE (@badcloc > 0)
	BEGIN
		SET @instr = SUBSTRING(@instr, 0, @badcloc) + SUBSTRING(@instr, @badcloc + 1, 1000)
		SET @badcloc = PATINDEX('%--%', @instr);
	END
	
	RETURN @instr;
END

Open in new window


AND

SELECT dbo.fixchars('Hello *&^%% GTN') as region

Open in new window


Gave me --

HelloGTN

Appreciate your help :)
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 39187696
Well... that was dumb of me.

Try this.  The bug was in the first line with SUBSTRING() calls.  I was, for some reason, assuming there would be a hyphen in there in the first place.

DROP FUNCTION [dbo].[fixchars]
GO

CREATE FUNCTION [dbo].[fixchars]
(@instr VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
      DECLARE @badcloc INT = PATINDEX('%[^a-zA-Z0-9-]%', @instr);
      WHILE (@badcloc > 0)
      BEGIN
            SET @instr = SUBSTRING(@instr, 0, @badcloc) + '-' + SUBSTRING(@instr, @badcloc + 1, 1000)
            SET @badcloc = PATINDEX('%[^a-zA-Z0-9-]%', @instr);
      END
      
      SET @badcloc = PATINDEX('%--%', @instr);
      WHILE (@badcloc > 0)
      BEGIN
            SET @instr = SUBSTRING(@instr, 0, @badcloc) + SUBSTRING(@instr, @badcloc + 1, 1000)
            SET @badcloc = PATINDEX('%--%', @instr);
      END
      
      RETURN @instr;
END
GO

SELECT dbo.fixchars('Hello *&^%% GTN') as region
0
 

Author Closing Comment

by:garethtnash
ID: 39188077
Perfect thank you

:)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question