Splitting a crazy column into two new columns

Transact-SQL
1

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

2 
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
3

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

4

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
5

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
6

Thanks for your help,