t-sql   535

« earlier    

SQL SERVER - Synchronize Data Exclusively with T-SQL - SQL Authority with Pinal Dave
Database developers often face the task to synchronize users' data. Currently, to solve this task, there was developed quite a big number of various utilities. In this article, we will review the process of creating the new functionality to synchronize data exclusively with T-SQL.
sql_server  data  synchronization  t-sql 
5 weeks ago by ritzlea
What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc? | Mark S. Rasmussen
What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc?
9 weeks ago by ienhienly
sql - How do I split a string so I can access item x? - Stack Overflow
This question is not about a string split approach, but about how to get the nth element.

All answers here are doing some kind of string splitting using recursion, CTEs, multiple CHARINDEX, REVERSE and PATINDEX, inventing functions, call for CLR methods, number tables, CROSS APPLYs ... Most answers cover many lines of code.

But - if you really want nothing more than an approach to get the nth element - this can be done as real one-liner, no UDF, not even a sub-select... And as an extra benefit: type safe

Get part 2 delimited by a space:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

Of course you can use variables for delimiter and position (use sql:column to retrieve the position directly from a query's value):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

If your string might include forbidden characters (especially one among &><), you still can do it this way. Just use FOR XML PATH on your string first to replace all forbidden characters with the fitting escape sequence implicitly.

It's a very special case if - additionally - your delimiter is the semicolon. In this case I replace the delimiter first to '#DLMT#', and replace this to the XML tags finally:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

shareimprove this answer
edited Mar 6 at 14:25
answered Jul 8 '16 at 20:41

This should be the correct answer! – Brendan Gooden Feb 28 '17 at 23:30
12 weeks ago by ienhienly
sql server - Show empty string when date field is 1/1/1900 - Stack Overflow
When you want a date to show as '' instead of NULL. A CASE WHEN ELSE '' statement doesn't behave as expected.
t-sql  howto 
may 2018 by ostertoaster

« earlier    

related tags

a  administration  advice  analysis  associate  azure  backups  big  bookmarks_bar_4  case  changes  check  circuit  client  code  codes  codestyle  coding  comments  common  completion  connection  control  convention  course  data  database  datatype  date  dateadd  datediff  dates  day  db  dba  devart  development  digit  docs  documentation  editor  etl  export  express  extensions  file  first  fix  for  formatter  formatting  full  functions  games  good_practice  guidelines  howto  ide  in  intellisense  into  javascript  json  language  last  link  linked  lock  login  man's  map  mdf  merge  microsoft  month  mssql  mssqm  multiline  multiple  optimizaiton  options  pending  performance  polybase  poor  powershell  programming  properties  query  read-only  reassociate  recover  recovering  recovery  refactor  repair  resources  restore  restoring  script  security  select  server  short  source  spaceused  sql-server  sql  sql_server  sqloperations  sqlserver  sqlsrv  ssis  ssms  stackoverflow  state  storage  studio  style  sybase  synchronization  table  tdd  test  testing  timeseries  tip  to  tools  transact  transaction  tsql  tutorial  udl  unit  unittesting  update  upsert  user  using  video  visual  windows  with  ‘restoring’ 

Copy this bookmark: