Splitting Domain from Domain\Username - With Multilanguage support ( Japanese, Dutch ... etc)

• Rajesh.NairNarayanan

  Grasshopper

  Points: 24

  More actions

  August 22, 2018 at 11:28 am

  #362201

  Hi,

  I need to Split Domain from (Domain\Username), and inset domain value in a new column. But character (' \ ') will be different in other languages like Japanese, Dutch ... etc.
  How can we write a generalized query that will work for any locale / collation.
  
  Below SQL Query will work for English usernames

  UPDATE UserProfiles SET Domain = SUBSTRING(username, 1, CHARINDEX('\', username) - 1) where username like '%\%'

  Also it Should work on Database : SQL SERVER 2008 R2, 2012, 2014, 2016 & 2017

  Please share your thoughts

  Thanks
  Rajesh Nair

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  August 22, 2018 at 3:48 pm

  #2002952

  You must use Unicode literal:

  N'/' instead of '/'.

  I assume, all columns and variables are defined as nvarchar, not varchar.

  _____________
  Code for TallyGenerator