Solved

MS SQL Replace Function

Posted on 2013-05-21
11
424 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
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!

 
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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

729 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