CODEWITHSUNDEEP
sqluser-defined-functions
donxythe
donxythe

Reputation: 151

Sql string manipulation within stored procedure

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

Upvotes: 2

Views: 4093

Answers (3)

donxythe
donxythe

Reputation: 151

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

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166486

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

Upvotes: 0

Andomar
Andomar

Reputation: 238186

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

Upvotes: 0

Related Questions