?
Solved

MS SQL Replace Function

Posted on 2013-05-21
11
Medium Priority
?
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

771 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