Finding in result set

• Darryl Meades-418843

  Valued Member

  Points: 53

  More actions

  March 15, 2007 at 3:17 am

  #175646

  Hi,

  First post, so please forgive me if the SQL terminology isn't quite right

  I have been asked to find where carriage returns and line feeds have been inserted in any part of the field CUSTOMER_PRODUCT.CUSP_Id.

  I already have a query that will look for these at the end of the field, as follows:

  set nocount on

  declare @selchar char

  select @selchar= CHAR(10)

  select

   CUS_Id,

   ART_Nr,

   CUSP_Id

  from

   CUSTOMER_PRODUCT

  where

   SUBSTRING(CUSP_Id, DATALENGTH(CUSP_Id), 1) = @selchar

  order by

   ART_Nr,

   CUSP_Id

  set nocount off

  Thanks for your help.

• Dave-3000

  SSCertifiable

  Points: 6186

  More actions

  March 15, 2007 at 12:56 pm

  #695331

  set nocount on

  declare @Result TABLE (String varchar(50))

  declare @String varchar(50), @10 char(3), @13 char(3), @9 char(3)

  SET @10 = '%' + char(10) + '%'

  SET @13 = '%' + char(13) + '%'

  SET @9 = '%' + char(9) + '%'

  SET @String = 'now is the time' + CHAR(10) + 'for all good men'

  INSERT INTO @Result VALUES (@String)

  SET @String = 'a carriage return' + CHAR(13) + 'in time'

  INSERT INTO @Result VALUES (@String)

  SET @String = 'tab tab tab tab' + CHAR(9) + 'and more tabs'

  INSERT INTO @Result VALUES (@String)

  select * from @Result

   where (patindex(@10, String) <> 0)

   OR

    (patindex(@13, String) <> 0)

   OR

    (patindex(@9, String) <> 0)

  There is no "i" in team, but idiot has two.