1

I have a table named "t", with column, "x". It looks as follows:

x
------
1.Fred
2.Joe
3.Hank
.
.
.
500.Mary

I need to take the column and remove everything before the name, for example the "1." before Fred.

I have tried update $2_'string x from t but that only works for numbers 1-9 not once I get to the larger numbers. Is there a way to remove the "." and everything before it?

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
michaelg
  • 243
  • 2
  • 8
  • 25

5 Answers5

6

You can use 0: for this - it's often the best approach for lists of strings, and works particularly well for longer lists compared to using find and drop, or vs:

q)update raze(" *";".")0:string c from t
x
----
Fred
Joe
Hank
Mary

This is efficiently splitting the string on the ".", throwing away the first column of the return (by not specifying it in " *", the first element of the left arg to 0:), and then giving an enlisted return, which needs to be razed to return it to a list of strings which will fit back in the column.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Ryan McCarron
  • 889
  • 4
  • 10
3

Assuming x is a column of symbol type:

q)update `$last each "."vs'string x from t
x
----
Fred
Joe
Hank
Mary

This utilizes a combination of the function vs with the adverb each-both: http://code.kx.com/q/ref/casting/#vs http://code.kx.com/q/ref/adverbs/#each-both

jomahony
  • 1,662
  • 6
  • 9
2

There is one more approach, which helps to cover case when name has '.':

update x:`${(1+x ?\:".")_'x} string x from  t

The logic is

  1. Convert symbol column x to string
  2. Get index of first dot in every element of list of strings 1+x ?\:"."
  3. Cut characters before the first dot (1+x ?\:".")_'x

Next model can be used for testing:

//Create table with 100 random names
t: ([] ID: til 100; x: `$(string 1+til 100),'".",'(?[;"abc"] each 1+100?9),'" ",'(?[;"def"] each 1+100?9));
//Replace space with dot for the last 10 names
t: update x: `$ssr[;" ";"."]'[string x] from t where ID>90;
update x:`${(1+x ?\:".")_'x} string x from  t

Though using 0: proposed by @Ryan McCarron is obviously faster. If you sure there are no additional dots, that approach is preferable.

Anton Dovzhenko
  • 2,399
  • 11
  • 16
0

Another way of getting it done is:

q)select (` vs/:x)[;1] from t
x
----
Fred
Joe
Hank
nyi
  • 3,123
  • 4
  • 22
  • 45
0

Another Way to get this result by using inter and .Q namespace

 t:update {`$x inter .Q.a,.Q.A} each string x from t
    
    x
    ----
    Fred
    Joe
    Hang

https://code.kx.com/q/ref/dotq/#qa-upper-case-alphabet
https://code.kx.com/q/ref/inter/

astin dinesh
  • 1
  • 1