SQL Server 2008 - 如何将 ntext 转换为 int 列表？

Question

我有类型为 ntext 的列，我想将其转换为 int 列表。列名为 ValidForCustomers，包含以逗号分隔的客户 ID。

我知道我可以将其转换为 nvarchar(max) 但当我这样做时 SQL 会抛出错误

“.”附近的语法不正确。

查询的一部分

select Items from Split(c.ValidForCustomers, ',')

这是查询

select top 100 
*,c.CouponCode, 
c.Description,
case when c.CouponType = 0 then 'Order - this coupon ONLY applies to the order subtotal'
else 'Product - this coupon ONLY applies to the specified product(s)' end as CouponType,
case when c.DiscountIncludesFreeShipping = 0 then 'No' else 'Yes' end as FreeShiping,
case when c.ExpiresAfterOneUsageByEachCustomer = 0 then 'No' else 'Yes' end as 'ExpiresAfterOneUsageByAnyCustomer',
case when c.ExpiresOnFirstUseByAnyCustomer = 0 then 'No' else 'Yes' end as 'ExpiresOnFirstUseByAnyCustomer',
c.ExpiresAfterNUses,
case when len(CAST(c.ValidForCustomers as nvarchar(max))) = 0 THEN 'No'
    ELSE cstms.CustomerNames END as 'ValidForCustomers',
CASE when LEN(CAST(c.ValidForProducts as nvarchar(max))) = 0 THEN 'No' ELSE 'No' END as 'ValidForProducts',
CASE when LEN(CAST(c.ValidForCategories as nvarchar(max))) = 0 THEN 'No' ELSE 'Yes' END as 'ValidForCategories'
    from Coupon c with(nolock)        
    cross apply ( select CustomerNames = (SELECT SUBSTRING((SELECT ',' + cust.FirstName + ' ' + cust.LastName
                FROM Customer cust
                inner join (select IntegerFromList from dbo.fn_StringListToIntList(CAST(c.ValidFOrCustomers as nvarchar(max)), ',')) sp on (sp.IntegerFromList= cust.CustomerID)                
                ORDER BY cust.FirstName
                FOR XML PATH('')),2,200000))) as cstms

这是转换过程

CREATE FUNCTION [dbo].[fn_StringListToIntList]
(
  @IntegerList  NVARCHAR(MAX),
  @Delimiter    CHAR(1) = '|'
)
RETURNS @IntegersTable TABLE (IntegerFromList  INT)
AS
BEGIN
  IF (@IntegerList IS NULL) OR (LEN(@IntegerList) = 0) OR (@Delimiter IS NULL) RETURN

  DECLARE  @DelimPos INT
  SELECT  @DelimPos = PATINDEX('%' + @Delimiter + '%', @IntegerList)

  WHILE @DelimPos <> 0
  BEGIN
    --If nothing between delims, save as NULL
    IF LEN(SUBSTRING(@IntegerList, 1, @DelimPos - 1)) = 0
      INSERT INTO @IntegersTable(IntegerFromList)  VALUES(NULL)
    ELSE
      INSERT INTO @IntegersTable(IntegerFromList)
      VALUES(CONVERT(INT, SUBSTRING(@IntegerList, 1, @DelimPos - 1)))

    SELECT @IntegerList  = SUBSTRING(@IntegerList, @DelimPos + 1, LEN(@IntegerList))
    SELECT @DelimPos  = PATINDEX('%' + @Delimiter + '%', @IntegerList)
  END --While...

  --If no additional chars after a final delim, treat as an additional NULL
  IF LEN(@IntegerList) = 0
    INSERT INTO @IntegersTable(IntegerFromList)  VALUES(NULL)
  ELSE
    INSERT INTO @IntegersTable(IntegerFromList)  VALUES(CONVERT(INT, @IntegerList))

  RETURN 

END --Function

，错误是

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '('.

，语法一切正常

Answer 1

这对于 cast 来说是一个很好的语法：

select  *
from    dbo.Split(cast(c.ValidForCustomers as nvarchar(max)), ',')

这会产生错误吗？

当您调用用户定义的函数时，架构 dbo 不是可选的。

Answer 2

由于所有号码都是客户的 ID，因此您可以做的是检查每个客户的 ID，以检查其是否在列表中。

SELECT Customer.Id
FROM MyTableWithTextField
INNER JOIN Customer
ON MyTableWithTextField.TextField LIKE CAST(Customer.Id AS VARCHAR(10)) + ',%'
OR MyTableWithTextField.TextField LIKE '%,' + CAST(Customer.Id AS VARCHAR(10)) + ',%'
OR MyTableWithTextField.TextField LIKE '%,' + CAST(Customer.Id AS VARCHAR(10))

是的，这很可怕，并且可能会表现得很差，但如果您尝试在单个字段中存储可变数量的值，这是不可避免的。更好的主意是创建一个新表。

Answer 3

我已经创建了用于返回客户字符串的自定义函数，

ALTER FUNCTION [dbo].[fn_GetCustomersForCoupon](
    @CouponID int
)
returns nvarchar(MAX)
as
Begin
declare @Str nvarchar(max)
declare @IntTable table ( ID int)
declare @ValidForProducts nvarchar(2000)

select @ValidForProducts = ValidForCustomers from Coupon where CouponID=@CouponID
insert into @IntTable (ID) select * from dbo.fnStringListToIntList(@ValidForProducts, ',')
SELECT @Str = SUBSTRING((select ',' + Replace(p.FirstName + ' ' + p.LastName, '<Name>','') from @IntTable t
inner join Customer p on (p.CustomerID = t.ID)
FOR XML PATH('')),2,200000)

return @Str
end

现在它可以作为一个魅力，但直接来自查询，作为子查询它不起作用。