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.