Equivalent of Substring in KDB on table column

Community Support
1

Hello,

I am newbie to KDB/q world. I have a  table t where in one of the column col1, I need to strip all values before # sign. Example if certain rows have “126734i#1”, my select query needs to have only 126734i. I am running below query and getting an error type

select distinct t:string “#” vs col1  from t where date within  (2017.11.01;2017.11.30)

I thought let me cast to string, but I am getting a length error in below query

select distinct t:string “#” vs col1 from td where date within  (2017.11.01;2017.11.30)

 I will really appreciate quick help.

Thanks

2

string it first, and then vs each-right of the column

q)t:(col1:1#`$“126734i#1”;date:1#2017.11.01)

q)select distinct t:(“#” vs/:string col1)[;0]  from t where date within  (2017.11.01;2017.11.30)

t

“126734i”

Or albeit more efficient - use zero-colon to parse and prepare the column

q)select distinct t:("S# "0:/:string col1)[;0] from (2#t) where date within  (2017.11.01;2017.11.30)

t

126734i

But - important - that’s going to create symbols from the ‘number+i’ and bloat the sym pool…so if you are actually are looking to split on the “i” rather than the ‘#’ (i.e. just capture the integers) then the following should be suffice

q)select distinct t:("Ii "0:/:string col1)[;0] from (2#t) where date within  (2017.11.01;2017.11.30)

t

126734

HTH,

Sean

3

Thanks Sean. I was also able to make it work through

update col1_t:{first “#” vs x} each col1 from select distinct col1 from t where date within (2017.11.01;2017.11.30)