none
Adding criteria for values when charindex returns 0 to return as well RRS feed

  • Question

  • What I am attempting to do is separate my data by the line breaks into separate fields: Attn, Addr1Field, Addr2Field. The address field is separated by char(10) + char(13) breaks. I want to be able to use the second expression of my WHERE clause in the Addr1Field to filter but I also want the rest of the records as well. When I remove (CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+1) -
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet)) > 0) from the query I get an error. How do I get all of the records?
    Select somast.fsono, soship.fmstreet,
    
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:'
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),7,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))-1)
    Else ''
    End as Attention,
    
    case
    when LEFT(CONVERT(VARCHAR(MAX), soship.fmstreet),5)='ATTN:' 
    Then SUBSTRING(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+2,CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+1) - 
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))) 
    
    
    Else Left(CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))) --substring for when attention is not in data
    End as Address1
    
    
    from soship inner join somast on soship.fcsono=somast.fsono
    where somast.fsono>='034023'     
    and (CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet),CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet))+1) - 
    CHARINDEX(CHAR(13)+CHAR(10),CONVERT(VARCHAR(MAX), soship.fmstreet)) > 0)


    Monday, September 17, 2018 2:22 PM

All replies

  • Use a split function:

    CREATE FUNCTION [dbo].[split](
              @delimited NVARCHAR(MAX),
              @delimiter NVARCHAR(100)
            ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
            AS
            BEGIN
              DECLARE @xml XML
              SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    
              INSERT INTO @t(val)
              SELECT  r.value('.','nvarchar(MAX)') as item
              FROM  @xml.nodes('/t') as records(r)
              RETURN
            END
    
    GO
    DECLARE @testaddr VARCHAR(MAX);
    SET @testaddr='Attn string' + CHAR(13) + CHAR(10) + 'Addr1Field' + CHAR(13) + CHAR(10) + 'Addr2Field';
    
    DECLARE @tbl TABLE (fsono INT, fmstreet VARCHAR(MAX));
    
    INSERT INTO @tbl (fsono, fmstreet) VALUES (1,@testaddr);
    
    SELECT 
        a.fsono,
        MAX(a.Attention) AS Attention,
        MAX(a.Address1) AS Address1,
        MAX(a.Address2) AS Address2
    FROM (
        SELECT 
    	   soship.fsono,
    	   Attention = CASE WHEN [id] = 1 THEN s.val ELSE NULL END,
    	   Address1 = CASE WHEN [id] = 2 THEN s.val ELSE NULL END,
    	   Address2 = CASE WHEN [id] = 3 THEN s.val ELSE NULL END
        FROM @tbl soship
    	   CROSS APPLY dbo.split(soship.fmstreet,CHAR(13) + CHAR(10)) as s
    ) a
    GROUP BY a.fsono
    

    Monday, September 17, 2018 2:59 PM
    Moderator
  • Are there always only 3 values? Please post some sample and explain how you want individual values to come?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, September 17, 2018 3:21 PM
  • Are there always only 3 values? Please post some sample and explain how you want individual values to come?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------

    There aren't always 3 values. 

    fsono  |                     fmstreet                               | Attention       |          Address 1     |   Address 2|

    034023|ATTN: Suzanne  5757 Kopetsy Drive  Suite E| Suzanne          |   5757 Kopetsy Drive|   Suite E

    034025|ATTN: Store Manager  7979 US 41             | Store Manager    | 7979 US 41|

    034027|ATTN: Gaye Burger 702 County Road 931   | Gaye Burger        | Country Road 931|

    034126| ATTN: Autumn Snowden  2913 US Highway 70  MF: Ingles #761Autumn Snowden2913 US Highway 70MF: Ingles #761

    Monday, September 17, 2018 3:37 PM
  • I'm getting a syntax error near '.' on the CROSS APPLY line.
    Monday, September 17, 2018 3:39 PM
  • I'm getting a syntax error near '.' on the CROSS APPLY line.

    Tom's code ran without syntax error when I tested it.

    What version of SQL Server are you using and what compat level does your database have?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 17, 2018 10:11 PM
  • I'm getting a syntax error near '.' on the CROSS APPLY line.

    Tom's code ran without syntax error when I tested it.

    What version of SQL Server are you using and what compat level does your database have?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    I'm using SQL Server 2008 R2
    Tuesday, September 18, 2018 1:07 AM
  • I'm using SQL Server 2008 R2

    Hi David9501,

    Could you please provide the detailed version of your SQL Server and Compatibility Level of the Database that you're using by executing following command?

    SELECT @@version
    
    SELECT compatibility_level  
    FROM sys.databases where name='Your Database name'

    But we execute it and haven't got any error message, it is very strange.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 18, 2018 6:10 AM
    Moderator
  • I'm using SQL Server 2008 R2

    Hi David9501,

    Could you please provide the detailed version of your SQL Server and Compatibility Level of the Database that you're using by executing following command?

    SELECT @@version
    
    SELECT compatibility_level  
    FROM sys.databases where name='Your Database name'

    But we execute it and haven't got any error message, it is very strange.

    Best Regards,

    Will

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

    Compatibility level: 80

    Tuesday, September 18, 2018 1:15 PM
  • You need to change  your database COMPATIBILITY_LEVEL to 90 or 100;

     

    ALTER DATABASE yourDB SET COMPATIBILITY_LEVEL = 100; GO

    Tuesday, September 18, 2018 1:22 PM
    Moderator
  • Are there always only 3 values? Please post some sample and explain how you want individual values to come?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------

    There aren't always 3 values. 

    fsono  |                     fmstreet                               | Attention       |          Address 1     |   Address 2|

    034023|ATTN: Suzanne  5757 Kopetsy Drive  Suite E| Suzanne          |   5757 Kopetsy Drive|   Suite E

    034025|ATTN: Store Manager  7979 US 41             | Store Manager    | 7979 US 41|

    034027|ATTN: Gaye Burger 702 County Road 931   | Gaye Burger        | Country Road 931|

    034126| ATTN: Autumn Snowden  2913 US Highway 70  MF: Ingles #761Autumn Snowden2913 US Highway 70MF: Ingles #761

    Sorry this is not not straightforward

    As per this data, there's no way by which you can generalize and determine which part of the value belongs to Attention, Address1 and Address 2. Unless there's a consistent delimiter you cant apply any logic reliably to get them separated onto different columns!


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 18, 2018 1:24 PM
  • Is there a reason you are running SQL 2000 compatibility level on your database??  You really need to upgrade to a current version.

    Tuesday, September 18, 2018 2:04 PM
    Moderator
  • You need to change  your database COMPATIBILITY_LEVEL to 90 or 100;

     

    ALTER DATABASE yourDB SET COMPATIBILITY_LEVEL = 100; GO


    This worked but now I have a new error XML parsing: line 1, character 13, illegal name character
    Tuesday, September 18, 2018 2:55 PM
  • Is there a reason you are running SQL 2000 compatibility level on your database??  You really need to upgrade to a current version.


    Honestly I have no idea. It's the system I inherited when I started working here.
    Tuesday, September 18, 2018 2:57 PM
  • This worked but now I have a new error XML parsing: line 1, character 13, illegal name character

    That's because some birght person gave you the code for a bad string splitter. Converting a list of numbers to XML to split it works, but using the same trick to split of strings is bound to end in tears.

    You find a better string splitter in this article on my web site: Arrays and Lists in SQL Server

    As for changing the compatibility level, CROSS APPLY does indeed not work in compat level 80. Unfortunately, I don't think it is a wise move to change it to 100, as there is a non-neglibile risk that other things will start to break. For instance, there could be occurrances of the old outer-join operators *= and =*. If you are playing around in a database of your own, I guess you can move to 100 to play with this, but it may not work that well in production.

    Then again, at some point your organisation need to move on to a higher version of SQL Server, and by then you need to have fixed all these legacy things. Compat level 80 is not supported in SQL 2012 and later.

    By the way, you (or your DBA) should apply SP3, the last service pack for SQL 2008 R2 to this instance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, September 18, 2018 10:11 PM
  • Honestly I have no idea. It's the system I inherited when I started working here.

    Hi David9501,

    My suggestion is that you need to install a new SQL Server of higher version (e.g. SQL Server 2012, SQL Server 2016), Then move data and objects of old server on to the new server by using Data migration tool. For the data migration tool, you could consider using the tool like SSIS, SQL Server Import and Export Wizard and so on.

    Because the Compatibility level 80 belongs to version of SQL 2000, and it is beyond support, some good and new features are not available on that version.

    Although we have good solution to help you solve your problem, it is not useful for that version.

    For that version, we don't have any better idea. Although the solution could work, we think it not very good for other versions. And it would guide us back to original complex SQL statements.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 19, 2018 8:38 AM
    Moderator