Validating Excel using XML and moving to SQL Server destination

Is there a built in function (as opposed to a UDF) or can someone provide sample code to split a String to two columns when a charact开发者_如何学JAVAer is encountered? Sample:

1234:abcd

split the above string into 1234 and abcd into two columns

Have a go with this. Its not pretty but it produces the two columns (assuming : is always the divider):

declare @test varchar(20)
set @test = '1234:abcd'

select 
    leftcol = left(@test,charindex(':',@test)-1),
    rightcol = right(@test,len(@test) - charindex(':',@test))

In otherwords, its not a build in function, but it is inline sql code.

Title/tag mismatch?

For Excel, if A1 contains the value:

make B1 =LEFT(A1,IF(ISERROR(FIND(":",A1)),LEN(A1),FIND(":",A1)-1))
make C1 =RIGHT(A1,IF(ISERROR(FIND(":",A1)),0,LEN(A1)-FIND(":",A1)))

Or for T-SQL + a string variable;

DECLARE @F VARCHAR(64) = '1234:ABCD'

IF @F LIKE '%:%' 
    SELECT SUBSTRING(@F, 1, CHARINDEX(':', @F, 1) - 1) AS COL1, 
            SUBSTRING(@F, CHARINDEX(':', @F, 1) + 1, LEN(@F)) AS COL2
ELSE
    SELECT @F AS COL1, NULL AS COL2

for a select;

;WITH faketable (fld) AS (
    SELECT 'aaa:123' as fld
    UNION SELECT 'ddddd'
)
SELECT 
    CASE WHEN fld LIKE '%:%' THEN SUBSTRING(fld, 1, CHARINDEX(':', fld, 1) - 1) ELSE fld END AS COL1 ,
    CASE WHEN fld LIKE '%:%' THEN SUBSTRING(fld, CHARINDEX(':', fld, 1) + 1, LEN(fld)) ELSE NULL END AS COL2 
FROM faketable

>COL1   COL2
>aaa    123
>ddddd  NULL