CODEWITHSUNDEEP
kdb+
Bogey
Bogey

Reputation: 5724

kdb+/q table - convert string to number

assume you have a table

tbl:([] id:("123"; ""; "invalid"))

And want to parse this string into a number.

Invalid values - in the example above, both the empty string "" as well as the value "invalid", should be parsed to null (0Nj).

How can you best do it? My initial approach was

select id:.[value;;0Nj] each enlist each id from tbl

But while that will parse the both the "123" as well as "invalid" entries correctly, it will return the unary operator :: instead of null when trying to parse the row with the empty string.

Of course I could do something like

select id:.[value;;0Nj] each enlist each id from update id:string (count id)#`invalid from tbl where id like ""

but this seems kind of.. ugly/inefficient. Is there any better way to do this?

Thanks

Upvotes: 0

Views: 5939

Answers (2)

terrylynch
terrylynch

Reputation: 13572

Try "J"$ to cast the column

q)select "J"$id from tbl
id
---
123

https://code.kx.com/v2/ref/tok/

Upvotes: 3

Davis.Leong
Davis.Leong

Reputation: 122

how about just cast it to long?

q)update id:"J"$id from `tbl
`tbl
q)select from tbl where not null id
id
---
123

Upvotes: 1

Related Questions