t-sql   541

« earlier    

SQL Server 2019 preview combines SQL Server and Apache Spark to create a unified data platform - SQL Server Blog
Break down data silos and deliver one view across all of your data using data virtualization. Starting in SQL Server 2016, PolyBase has enabled you to run a T-SQL query inside SQL Server to pull data from your data lake and return it in a structured format—all without moving or copying the data. Now in SQL Server 2019, we’re expanding that concept of data virtualization to additional data sources, including Oracle, Teradata, MongoDB, PostgreSQL, and others. Using the new PolyBase, yo...
Break  down  data  silos  and  deliver  one  view  across  all  of  your  using  virtualization.  Starting  in  SQL  Server  2016  PolyBase  has  enabled  you  to  run  a  T-SQL  query  inside  pull  from  lake  return  it  structured  format—all  without  moving  or  copying  the  data.  Now  2019  we’re  expanding  that  concept  virtualization  additional  sources  including  Oracle  Teradata  MongoDB  PostgreSQL  others.  new  can  easily  combine  many  avoid  time  effort  security  risks  duplicate  created  by  movement  replication.  elastically  scalable  “data  pools”  “compute  make  querying  virtualized  lighting  fast  caching  distributing  execution  instances  Server. 
10 weeks ago by jamrock
How to drop a table if it exists in SQL Server? - Stack Overflow
IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL
september 2018 by ienhienly
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?
june 2018 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
may 2018 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

2016  2019  a  across  additional  administration  advice  all  alwayson  analysis  and  associate  avoid  azure  backups  beautify  big  bookmarks_bar_4  break  by  caching  can  case  changes  check  circuit  client  code  codes  codestyle  coding  combine  comments  common  completion  concept  connection  control  convention  copying  course  created  data.  data  database  datatype  date  dateadd  datediff  dates  day  db  dba  deliver  devart  development  digit  distributing  docs  documentation  down  duplicate  easily  editor  effort  elastically  enabled  etl  execution  expanding  export  express  extensions  failover  fast  file  first  fix  for  formatter  formatting  format—all  from  full  functions  good_practice  guidelines  hadr  has  howto  ide  in  including  inside  instances  intellisense  into  it  javascript  json  lake  language  last  lighting  link  linked  lint  lock  login  make  man's  many  map  mdf  merge  microsoft  mongodb  month  movement  moving  mssql  mssqm  multiline  multiple  new  now  of  one  online  optimizaiton  options  or  oracle  others.  pending  performance  polybase  pools”  poor  postgresql  powershell  programming  properties  pull  query  querying  read-only  reassociate  recover  recovering  recovery  reference  repair  replication.  resources  restore  restoring  return  risks  run  scalable  script  security  select  server.  server  short  silos  source  sources  spaceused  sql-server  sql  sqloperations  sqlserver  sqlsrv  ssis  ssms  starting  state  storage  structured  style  sybase  table  tdd  teradata  test  testing  that  the  time  tip  to  tools  transact  transaction  tsql  tutorial  udl  unit  unittesting  update  updates  upsert  user  using  video  view  virtualization.  virtualization  virtualized  we’re  windows  with  without  you  your  ‘restoring’  “compute  “data 

Copy this bookmark: