Sql string manipulation within stored procedure

Question

I have a field which contains article titles. I need to create friendly or pretty url's out of the article titles.

I need help manipulating the string with SQL. This has to be done within a stored procedure or function.

The requirements: The only characters allowed are lowercase letters and numbers (a-z and 0-9) All spaces need to be replaced with dashes.

Thanks!

(updated) I am using Sql Server 2008

I don't think that would be a good idea since you might end up with different items having the same URL. And to account for that in a stored procedure would significantly complicate the logic. Perhaps doing it outside your database would be a better option, whether you're going to do it often (e.g. on selection or insertion), or once off. — Mhmmd, Aug 19 '10 at 03:57
@OMG Ponies Sql Server 2008 @ShaderOp I already have the functionality in place to take care of duplicates. I was planning on doing all the manipulation before inserting the data but, it turns out most of the data is is already inserted in the database — donxythe, Aug 19 '10 at 05:12

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

I found the answer over here. Thank you all! How to strip all non-alphabetic characters from string in SQL Server?

CREATE Function [dbo].[create_pretty_url](@Temp VarChar(1000))

Returns VarChar(1000)

AS Begin

While PatIndex('%[^A-za-z0-9]%', @Temp) > 0
    Set @Temp = LOWER(Stuff(@Temp, PatIndex('%[^A-za-z0-9]%', @Temp), 1, ''))

Return @Temp

End

edited May 23 '17 at 12:09

Community

1
1

answered Aug 19 '10 at 06:27

donxythe

151
1
3
9

Woops. spoke to soon. I need to replace spaces with dashes first... any ideas? – donxythe Aug 19 '10 at 06:34
Set @Temp = (Stuff(@Temp, PatIndex('%[^A-za-z0-9 ]%', @Temp), 1, '')) Set @Temp = Replace(LOWER(@Temp),' ','-') – donxythe Aug 19 '10 at 06:59

score 0 · Answer 2 · answered Aug 19 '10 at 04:05

To check for lowercase letters, you can use a binary collation like Latin1_General_BIN.

This SQL Server procedure checks if a string contains only spaces, digits or lowercase letters. If so, it returns 1 and replaces spaces with underscores. Else it returns -1.

if OBJECT_ID('TestProc') is null
    exec ('create procedure TestProc as select 1')
go
alter procedure TestProc(
    @str varchar(256),
    @result varchar(256) output)
as
begin
set @result = null
set @str = REPLACE(@str,' ','_')
if @str like '%[^0-9a-z_]%' collate Latin1_General_BIN
    return -1
set @result = @str
return 1
end
go

Test data:

declare @rc int
declare @result varchar(256)
exec @rc = TestProc '11 aa', @result out
select @rc, @result
exec @rc = TestProc 'NO CAPS', @result out
select @rc, @result
exec @rc = TestProc '%#$#$', @result out
select @rc, @result

-->

1           11_aa
-1          NULL
-1          NULL

score 0 · Answer 3 · answered Aug 19 '10 at 04:13

0

You did not state which database, or version for that matter, but lets go with:

I you were to be using Sql Server 2005, 2008, have a look at using CLR functions

Adding Regular Expressions (Regex) to SQL Server 2005

answered Aug 19 '10 at 04:13

Adriaan Stander

162,879
31
289
284

Sql string manipulation within stored procedure

3 Answers3