-1

I am using SQL Server 2008 R2

This is the value that I have :

DECLARE @DBB varchar(200) = 'A2gg3h.B2g3ghh3.Cggh3663.D1jhg23.Eh2hjj2g'

Returning the 2 outer values are easy enough :

SELECT LEFT(@DBB, CHARINDEX('.', @DBB)-1)
SELECT RIGHT(@DBB, CHARINDEX('.', @DBB)-1)

How would I alter script in order to select values :

1. 'Bg2g3ghh3'
2. 'Chggh3663'
3. 'Dh1jhg23'

Using CHARINDEX would only bring back (LEFT) 7 and (RIGHT) 9.

Thanks

PKirby
  • 859
  • 3
  • 16
  • 36
  • 1
    http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x – CodingDefined Dec 02 '14 at 11:36
  • This is a duplicate of so many questions, none of them (in my opinion) will give you a better answer than reading the following three articles: [Splitting strings the right way - or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings: A Follow-up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up), and [Splitting Strings : Now with less T-SQL](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) – GarethD Dec 02 '14 at 11:47

2 Answers2

1

Use this.

DECLARE @param NVARCHAR(MAX)
SET @param = 'A2gg3h.B2g3ghh3.Cggh3663.D1jhg23.Eh2hjj2g'

SELECT 
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
FROM  
(
    SELECT CAST ('<M>' + REPLACE(@param, '.', '</M><M>') + '</M>' AS XML) AS CVS 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
Saravana Kumar
  • 3,669
  • 5
  • 15
  • 35
1

TRY THIS:

DECLARE @string VARCHAR(MAX),
@Split CHAR(1),
@X xml
SELECT @string = 'A2gg3h.B2g3ghh3.Cggh3663.D1jhg23.Eh2hjj2g',
@Split = '.'
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@string,@Split,'</s><s>') + '</s></root>')
SELECT T.c.value('.','varchar(max)') AS Result
FROM @X.nodes('/root/s') T(c)
Veera
  • 3,412
  • 2
  • 14
  • 27
  • Using XML extensions to split a string should only be done if you are 100% certain that the input will **never** contain `>`, or `<`, if it does the method will fail – GarethD Dec 02 '14 at 11:52