CODEWITHSUNDEEP
substringkdb+
mmv456
mmv456

Reputation: 13

kdb/q How do I split a column into two by the first 6 characters?

My table has a column like this:

DateName
2024.01.01Ann
2024.01.02Bob
2024.01.03Ant

I want to be able to split this column into two, where the first one is the Date from DateName and the second is the Name from DateName:

Date Name
2024.01.01 Ann
2024.01.02 Bob
2024.01.03 Ant

I know that I can cut using '10_DateName but I don't know how to split the first part and create a Date column from that and a Name column from the rest of the field. Looking for opinions on how I can approach, otherwise I was thinking of doing a 'first 10 characters' and 'rest' approach and adding new columns for each.

Upvotes: 0

Views: 201

Answers (2)

terrylynch
terrylynch

Reputation: 13657

In terms of a basic select statement you could do:

q)select Date:"D"$10#'DateName,Name:`$10_'DateName from t
Date       Name
------------------
2024.01.01 Anna
2024.01.02 Bob
2024.01.03 Anthony

but you would really make your life easier if you forced the upstream source to not append dates and names together. At the very least have delimiters put between them, then it becomes as simple as (similar to rian's answer):

q)t:([] DateName:("2024.01.01|Ann";"2024.01.02|Bob";"2024.01.03|Ant"));
q)exec flip`Date`Name!("DS";"|")0:DateName from t
Date       Name
---------------
2024.01.01 Ann
2024.01.02 Bob
2024.01.03 Ant

Upvotes: 1

rianoc
rianoc

Reputation: 3786

Sample table:

q)t:([] DateName:("2024.01.01Ann";"2024.01.02Bob";"2024.01.03Ant"))

q)t
DateName
---------------
"2024.01.01Ann"
"2024.01.02Bob"
"2024.01.03Ant"

Example using @ for apply and composition:

And \:/: each-left each-right iterators

q)flip exec `Date`Name!flip("D"$#[10] @;`$_[10] @)@\:/:DateName from t
Date       Name
---------------
2024.01.01 Ann
2024.01.02 Bob
2024.01.03 Ant

Or with composition removed:

q)flip exec `Date`Name!"DS"$flip(#[10];_[10])@\:/:DateName from t
Date       Name
---------------
2024.01.01 Ann
2024.01.02 Bob
2024.01.03 Ant

And simplified to only need 10 entered once:

q)flip exec `Date`Name!"DS"$flip((#;_)@\:10)@\:/:DateName from t
Date       Name
---------------
2024.01.01 Ann
2024.01.02 Bob
2024.01.03 Ant

Upvotes: 2

Related Questions