problem with sql function

I'm trying to create a procedure that would return a table with 'child numbers' of a given 8-digit number form database. For instance, a child of a number 54000000 would be 54100000, 54009900 54000001 and so on.

My idea is 开发者_如何学运维to declare a variable table (ParentPartTable) that would contain just the 'parent' part of a number (table, not a single value because we can pass more than one number whose childs we need) so in example above it would contain '54' and then do something like:

INSERT INTO @ReturnTable
SELECT n.Symbol, n.Id
FROM NumbersTable n
JOIN @ParentPartTable p
ON n.Symbol LIKE p.SYMBOL + '%';

Unfortunately this doesnt seem to work. Any idea what could be wrong?

This works.

DECLARE @ParentPartTable TABLE
(
SYMBOL VARCHAR(8) /*Don't use CHAR(8) here!*/
)

INSERT INTO @ParentPartTable VALUES ('54');

WITH NumbersTable AS
(
SELECT '54000000' AS Symbol, 1 AS Id
)

SELECT n.Symbol, n.Id
FROM NumbersTable n
JOIN @ParentPartTable p
ON n.Symbol LIKE p.SYMBOL + '%';

Are you sure you aren't using char(8)? As that would pad trailing spaces.

Assuming Symbol is numeric in both tables, you need to cast ti to varchar in the LIKE clausule.
ON n.Symbol LIKE cast(p.SYMBOL as varchar) + '%'; should work.