t-sql   526

« earlier    

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?
6 days 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
25 days 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 
5 weeks ago 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  data  database  datatype  date  dateadd  datediff  dates  day  db  dba  devart  development  digit  docs  documentation  editor  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  restore  restoring  script  security  select  server  short  source  spaceused  sql-server  sql  sqloperations  sqlserver  sqlsrv  ssis  ssms  stackoverflow  state  storage  studio  style  sybase  table  tdd  test  testing  timeseries  to  tools  transact  transaction  tsql  tutorial  udl  unit  unittesting  unpivot  update  upsert  user  using  visual  windows  with  ‘restoring’ 

Copy this bookmark: