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?

You really should have just store the number in a separate field. Now you'll have to use PADINDEX etc. to split the number and the text appart. — Serge, Jun 11 '13 at 12:06
Wouldn't do. In that case I would have to do "where name+'-'+serial = @url I never want to split. When creating a new seourl, I try to enter it. If it's already there, I try name-1, name-2,name-3 in a loop until I find an unused one. — Leif Neland, Jun 11 '13 at 14:31

score 0 · Answer 1 · answered Jun 11 '13 at 12:17

0

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)

answered Jun 11 '13 at 12:17

Serge

6,554
5
30
56

Did you even run the query? – Serge Jun 11 '13 at 14:34
Now I did, but as expected, it splits, it does not create the unique keys from non-unique titles. It does the reverse. – Leif Neland Jun 11 '13 at 19:58
Don't you want to update your seourl? If so, as far as I know, you can just change whatever is before - while keeping -. Don't you? – Serge Jun 12 '13 at 07:13
Read the question again. The title is the seourl, the number is only for separating items with the same title/size. The number is not unique. – Leif Neland Jun 12 '13 at 13:05
Please put a complete example of several old + new url you want. – Serge Jun 12 '13 at 13:10
If i have several red-sock the urls should be /red-sock /red-sock-1 /red-sock-2 /red-sock-3 My problem is I have to have a loop that for each row have a loop that tries to insert, if duplicate, increment number, then try again until url is unique, It takes ages... – Leif Neland Jun 12 '13 at 21:13

Serge · Answer 2 · 2013-06-13T07:26:35.857

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()

Creating readable, unique key based on fields

2 Answers2