Creating readable, unique key based on fields

Question

The webguys wants unique urls based on the name of the products If more products have the same name, add a number after the name.

our.dom/red-sock

our.dom/red-sock-1

They do not want the product id or another number on all products, i.e.

our.dom/red-sock-123481354

I store this in a field i call seourl.

I have it covered when I create new products, a trigger tries adding the seourl, if it is already there, increment the number, until an unique value is found.

But I now have to give the entire table new seourls. If I just

update tab set seourl=dbo.createurl(title)

I am sure to have collissions, and the operation is rolled back. Is there a way to have the statement to commit the updates that work, and leave the rest unchanged?

Or must I just do a RBAR, Row By Agonizing Row operation in a loop?

Answer 1

Adapt this to your needs:

select
*
from (values('aaa'), ('aaa-12'), ('aaa-'), ('bbb-3')) as src (x)
cross apply (
    select isnull(nullif(patindex('%-[0-9]%', x) - 1, -1), LEN(x))
) as p(idx)
cross apply (
    select
        SUBSTRING(x, 1, idx)
        , SUBSTRING(x, idx + 1, LEN(x) - idx)
) as t(t, xx)

Answer 2

Try this:

declare @tmp table (
    id int not null identity
    , name varchar(100) -- you need name to be indexed
    , urlSuffix int -- store the number (ot you'll have to use PATINDEX, etc. as previously shown)!
    , url as name + ISNULL('_' + cast(NULLIF(urlSuffix, 0) as varchar(100)), '')

    , unique (name, id) -- (trick) index on name
)

insert @tmp (name, urlSuffix)
select
    src.name
    , ISNULL(T.urlSuffix, -1) + ROW_NUMBER() OVER (PARTITION BY src.name ORDER BY (select 1))
from (values
    ('x')
    , ('y')
    , ('y')
    , ('y')
    , ('z')
    , ('z')
) as src (name)
left join (
    select
        name
        , MAX(T.urlSuffix) as urlSuffix
    from @tmp AS T
    GROUP BY name
) as T on (
    T.name = src.name
)

insert @tmp (name, urlSuffix)
select
    src.name
    , ISNULL(T.urlSuffix, -1) + ROW_NUMBER() OVER (PARTITION BY src.name ORDER BY (select 1))
from (values
    ('a')
    , ('b')
    , ('b')
    , ('b')
    , ('z')
    , ('z')
) as src (name)
left join (
    select
        name
        , MAX(T.urlSuffix) as urlSuffix
    from @tmp AS T
    GROUP BY name
) as T on (
    T.name = src.name
)

select
    name, url
from @tmp
order by url

The solution to yur problem should lies in the use of ROW_NUMBER()