garethtnash
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
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
ASKER
There's a learning curve.. Would there be a quicker way of doing it in VBScript?
Thanks
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:
Outputs:
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
Outputs:
hey
yoman
heythereman
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
ASKER
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
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.
ASKER
Hi Nemws1,
Yes, thats the function I just tested. removed all spaces also.. :(
I'll try it again
Yes, thats the function I just tested. removed all spaces also.. :(
I'll try it again
ASKER
Umm yes so --
AND
Gave me --
Appreciate your help :)
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
AND
SELECT dbo.fixchars('Hello *&^%% GTN') as region
Gave me --
HelloGTN
Appreciate your help :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect thank you
:)
:)
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