Sql CASE statement with SUBSTRING?

Hi I have a small query below :

SELECT  SubscriberDataId,
        SUBSTRING(FacetsData,  5,  9) SubscriberCode,
        SUBSTRING(FacetsData, 14, 35) SubscriberLastName,
        SUBSTRING(FacetsData, 50, 15) SubscriberFirstName
FROM    Facets.FacetsImpor开发者_如何学JAVAt
WHERE   DataIndicator = 'DEM1'

i am trying to use Case statement in this query but dont know how. what i have to do is i have to find the invalid records from the 3 fields above. so i am gonna use LEN(SubscriberCode) > 9 (9 is lenght of data type) and when its greater than 9 then insert it into error table. same way for other two columns having lenght 35 and 15.

Thanks

The strings will have the correct lengths if and only if the input string is 64 characters or longer:

INSERT
INTO    main_table
SELECT  SubscriberDataId,
        SUBSTRING(FacetsData,  5,  9) SubscriberCode,
        SUBSTRING(FacetsData, 14, 35) SubscriberLastName,
        SUBSTRING(FacetsData, 50, 15) SubscriberFirstName
FROM    Facets.FacetsImport
WHERE   DataIndicator = 'DEM1'
        AND LEN(FacetsData) >= 64

INSERT
INTO    error_table
SELECT  SubscriberDataId,
        SUBSTRING(FacetsData,  5,  9) SubscriberCode,
        SUBSTRING(FacetsData, 14, 35) SubscriberLastName,
        SUBSTRING(FacetsData, 50, 15) SubscriberFirstName
FROM    Facets.FacetsImport
WHERE   DataIndicator = 'DEM1'
        AND (LEN(FacetsData) < 64 OR FacetsData IS NULL)

You will not be able to test for >9, >35, >15 with substring because substring will give you up to the number of bytes requested. You would be better served to check the inputs outside of the database or see if there is an import tool for your DB that will do these types of checks for you.