Link to home
Create AccountLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MS SQL Replace Function

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
Avatar of Surendra Nath
Surendra Nath
Flag of India image

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
Avatar of garethtnash

ASKER

There's a learning curve.. Would there be a quicker way of doing it in VBScript?

Thanks
Is '&' the only exception that you want to convert to '-'? Or do you have any such other scenarios?
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

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

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
Did you try my latest function from the last post I made. Should be doing exactly that.
Hi Nemws1,

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

I'll try it again
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 :)
ASKER CERTIFIED SOLUTION
Avatar of Nem Schlecht
Nem Schlecht
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Perfect thank you

:)