I need to parse and change the field identifier

• randyetheridge

  SSCommitted

  Points: 1703

  More actions

  October 22, 2018 at 2:31 pm

  #371976

  I need to join a table to another table. In order to do so I need to change "-" to a "."

  so my table is this

  1234-123
  1235
  1236-124
  1237
  1238
  1239
  1240-250

  I need this
  1234.123
  1235
  1236.124
  1237
  1238
  1239
  1240.250

  I cannot figure out how to do this.  Field name is accountx tried this and it works, 
   

  SELECT CASE When LEN([accountx])> '4'

  THEN substring([ACCOUNTx],1,4)+'.'+ substring([ACCOUNTx],6,3)

  ELSE substring([ACCOUNTx],1,4)

  END

  AS JDEAccount

  FROM [MAX].[dbo].[MAXJDEtoInfinium]

  but account can have leading characters or 6 or 7.  So to allow for all possible combinations it became very cumbersome.
  7651234-123
  651235
  51236-124
  7651237
  651238
  7651239
  651240-250

  So any suggestions on cleaner code to accomplish want I need?

• ScottPletcher

  SSC Guru

  Points: 101101

  More actions

  October 22, 2018 at 2:36 pm

  #2010691

  Try this:

  REPLACE(accountx, '-', '.')

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• randyetheridge

  SSCommitted

  Points: 1703

  More actions

  October 22, 2018 at 3:59 pm

  #2010703

  Ok that was embarrassingly easy.   I searched the web, but kept looking for special character identification.  Never thought to look for replace.  thanks