[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

MS SQL Replace Function

Posted on 2013-05-21
11
Medium Priority
?
437 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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

591 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