SQLTeam.com | Weblogs | Forums

Splitting a crazy column into two new columns

Hi all,

I have some data which is provided, unfortunately I have no control over the format.

One crazy column contains some data i need to split into two columns, i can't even think how to do this...

So this is the crazy data in that column: Booked on: 20/03/2020 10:52 by: A1234567

Is there a way to Split the column so that the Date and time is joined and column named as "Booking date" and then a column called "By Who" containing just the ID at the end - ID will be a fixed length from checking the data.

I tried messing with split string, but i was hopeless

Any help appreciated.

Thanks

SELECT string, booked_on_date, by_id
FROM ( VALUES
    ('Booked on: 20/03/2020 10:52 by: A1234567'), 
    ('Booked on 20/03/2020 11:52 by:A1234567'), 
    ('Booked on20/03/2020 13:52 by A1234567') 
) AS test_data(string)
CROSS APPLY (
    SELECT CHARINDEX('Booked on', string) AS booked_on_literal,
        CHARINDEX(' by', string) AS by_literal 
) AS ca1
CROSS APPLY (
    SELECT
        booked_on_start, 
        CASE WHEN booked_on_literal = 0 THEN NULL
            ELSE CONVERT(datetime, SUBSTRING(string, booked_on_start, 10), 103) +
                CAST(SUBSTRING(string, booked_on_start + 11, 5) AS datetime)
            END AS booked_on_date,
        CASE WHEN by_literal = 0 THEN NULL
            ELSE LTRIM(SUBSTRING(string, by_literal + 4, 9))
            END AS by_id
    FROM (
        SELECT booked_on_literal + 8 + PATINDEX('%[0-9]%', SUBSTRING(string, booked_on_literal + 9, 20)) AS booked_on_start
    ) AS calc1
) AS ca2
2 Likes

Hi Scott,

Firstly, thank you so much for the script, it runs perfectly.

Can i ask how i change it so it uses my Column in the table, rather than the example data you added in your script.

Sorry if its obvious, kinda new to this.

Thanks again

Change the Values to the table name.

Create table #Test ( string varchar(100), 
					booked_on_date datetime, 
					by_id varchar(10))

insert into #test (String)
 VALUES   ('Booked on: 20/03/2020 10:52 by: A1234567'), 
    ('Booked on 20/03/2020 11:52 by:A1234567'), 
    ('Booked on20/03/2020 13:52 by A1234567')


SELECT string, ca2.booked_on_date, ca2.by_id
FROM #Test
CROSS APPLY (
    SELECT CHARINDEX('Booked on', string) AS booked_on_literal,
        CHARINDEX(' by', string) AS by_literal 
) AS ca1
CROSS APPLY (
    SELECT
        booked_on_start, 
        CASE WHEN booked_on_literal = 0 THEN NULL
            ELSE CONVERT(datetime, SUBSTRING(string, booked_on_start, 10), 103) +
                CAST(SUBSTRING(string, booked_on_start + 11, 5) AS datetime)
            END AS booked_on_date,
        CASE WHEN by_literal = 0 THEN NULL
            ELSE LTRIM(SUBSTRING(string, by_literal + 4, 9))
            END AS by_id
    FROM (
        SELECT booked_on_literal + 8 + PATINDEX('%[0-9]%', SUBSTRING(string, booked_on_literal + 9, 20)) AS booked_on_start
    ) AS calc1
) AS ca2
2 Likes

Change "string" and other column names to match the column names in your actual table.
Change the FROM to be your table.
Like this:

SELECT string, booked_on_date, by_id
FROM dbo.your_table_name
CROSS APPLY ...

1 Like

Thanks for your help,