SQL Version of CONCAT and LIMIT

Question

Trying to follow Calendar Recurring/Repeating Events - Best Storage Method to design the database using SQL Server 2005.

When i run this below query

SELECT EV.*
FROM events EV
RIGHT JOIN events_meta EM1 ON EM1.event_id = EV.id
RIGHT JOIN events_meta EM2 ON EM2.meta_key = CONCAT( 'repeat_interval_', EM1.id )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.meta_value )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.meta_value )
          END
        ) / EM2.meta_value
    ) = 1
LIMIT 0 , 30

I am getting 'CONCAT' is not a recognized built-in function name.

I searched and seems like it is supported only in SQL Server 2012. How can i acheive this with SQL Server 2005/2008? Also what about LIMIT syntax in SQL Server?

I want the above sql version of query.

GarethD · Answer 1 · 2013-11-29T11:03:28.873

The SQL Server equivalent of limit is TOP. e.g

SELECT TOP 10 ID
FROM    Table.

TOP is not really synomonous with LIMIT, because you can't pass 2 arguments to it, (e.g. LIMIT 10, 20) to specify the starting point. In SQL Server 2012 the equivalent is OFFSET FETCH, but in 2005 and 2008 versions you need to use ROW_NUMBER and filter on the value of this, e.g.

SELECT  *
FROM    (   SELECT *, RowNum = ROW_NUMBER() OVER(ORDER BY ID)
            FROM    T
        ) t
WHERE   t.RowNumber BETWEEN 10 AND 30;

You can just use + to concatenate strings:

SELECT 'a' + 'b'

SQL Server 2012 introduces the CONCAT function

score 1 · Answer 2 · answered Nov 29 '13 at 10:55

The equivalent function in SQL Server to CONCAT() is + and you can limit the resultset by using the TOP keyword.

Write your query as:

SELECT TOP 30 EV.*
FROM events EV
RIGHT JOIN events_meta EM1 ON EM1.event_id = EV.id
RIGHT JOIN events_meta EM2 ON EM2.meta_key = 'repeat_interval_' + CAST (EM1.id as varchar(10)) -- cast is required in case id column is int
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.meta_value )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.meta_value )
          END
        ) / EM2.meta_value
    ) = 1

sjkm · Answer 3 · 2013-11-29T11:07:12.903

1

assumed that EM1.id is of type int you first have to convert it to a varchar. To concatenate use the + operator. And use TOP instead of LIMIT in Sql-server.

This should work:

SELECT TOP 30 EV.*
FROM events EV
RIGHT JOIN events_meta EM1 ON EM1.event_id = EV.id
RIGHT JOIN events_meta EM2 ON EM2.meta_key = ('repeat_interval_' + CONVERT(nvarchar(max), EM1.id))
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1299132000 - EM1.meta_value )
            WHEN 0
              THEN 1
            ELSE ( 1299132000 - EM1.meta_value )
          END
        ) / EM2.meta_value
    ) = 1

edited Nov 29 '13 at 11:07

answered Nov 29 '13 at 10:57

sjkm

3,887
2
25
43

I question the fact that this is tested (at least on the correct DBMS) since `LIMIT` is not SQL Server Syntax... – GarethD Nov 29 '13 at 11:05
Oh, thank you. You're right of course. Updated the code. I tested the main issue (concatenation). – sjkm Nov 29 '13 at 11:08

SQL Version of CONCAT and LIMIT

3 Answers3