blog-lookup-table-6-team-lookup-table.scala

val teams = sqlContext.table("lookup_example_nhl")
  .withColumn("short_name",
    when(
      locate("New York", $"team") === 1,
      regexp_extract($"team", "\\w+$", 0)
    ).when(
      (locate("Devils", $"team") > 0) ||
      (locate("Kings", $"team") > 0) ||
      (locate("Sharks", $"team") > 0) ||
      (locate("Blues", $"team") > 0),
      regexp_extract($"team", "^(.*) \\w+", 1)
    ).otherwise(regexp_extract($"team", "^\\w+", 0))
  )

teams.show()

+--------------------+------------+------------------+------------+
|                team|    division|        conference|  short_name|
+--------------------+------------+------------------+------------+
|       Boston Bruins|    Atlantic|Eastern Conference|      Boston|
|      Buffalo Sabres|    Atlantic|Eastern Conference|     Buffalo|
|   Detroit Red Wings|    Atlantic|Eastern Conference|     Detroit|
|    Florida Panthers|    Atlantic|Eastern Conference|     Florida|
|  Montreal Canadiens|    Atlantic|Eastern Conference|    Montreal|
|     Ottawa Senators|    Atlantic|Eastern Conference|      Ottawa|
| Tampa Bay Lightning|    Atlantic|Eastern Conference|       Tampa|
| Toronto Maple Leafs|    Atlantic|Eastern Conference|     Toronto|
| Carolina Hurricanes|Metropolitan|Eastern Conference|    Carolina|
|Columbus Blue Jac...|Metropolitan|Eastern Conference|    Columbus|
+--------------------+------------+------------------+------------+