need help on a query using substring

Question

Hi I have a scenario like

I/P

Body Type   Name

123-4567-89 1 xyz

12-3-45-89 2 abc

O/P

Body Type Name

123 1 xyz

4567 1 xyz

89 1 xyz

12 2 abc

3 2 abc

45 2 abc

89 2 abc

Can you please help on this??

Thanks

Raaz

Answer 1

Try -

--Split function - Create the function
CREATE FUNCTION [dbo].[ufn_SplitIntArray] (
  @List VARCHAR(max),
  @Delimiter CHAR(1)
  )
 RETURNS @Items TABLE (Item VARCHAR(10))
 AS
 BEGIN
  DECLARE @Item VARCHAR(12),
   @Pos INT
  WHILE len(@List) > 0
  BEGIN
   SET @Pos = charindex(@Delimiter, @List)
   IF @Pos = 0
    SET @Pos = len(@List) + 1
   SET @Item = left(@List, @Pos - 1)
   INSERT @Items
   SELECT ltrim(rtrim(@Item))
  --SELECT convert(INT, ltrim(rtrim(@Item))) 
   SET @List = substring(@List, @Pos + len(@Delimiter), len(@List))
  END
  RETURN
 END
GO
--Example using the above created function
DECLARE @t TABLE(Body VARCHAR(15),TypeName VARCHAR(15))
INSERT INTO @t(Body,TypeName)
VALUES('123-4567-89','1xyz'),('12-3-45-89','2abc')
SELECT cply.Item,TypeName
FROM @t 
CROSS APPLY dbo.ufn_SplitIntArray(Body,'-') AS cply

---

Narsimha

Answer 2

That is an awfully weird requirement.  And to match, this is an awfully weird query.  The ROW_NUMBER function did not behave at all as I expected.  But, it works, I guess.

Declare @tvTable Table (
	BodyType	varchar(20)
	,BodyName	varchar(20)
)

Insert	@tvTable
Values	('123-4567-89 1', 'xyz')
		,('12-3-45-89 2', 'abc')

;with cte as
(
	Select	split.Item BodyTypePartThing
			,t.BodyType
			,ROW_NUMBER() OVER(Partition By t.BodyType Order By t.BodyType) RowNumber
	From	@tvTable t
	Cross Apply adv.fncDelimitedSplit8K(t.BodyType, ' ') split
)
Select	s1.Item + ' ' + c2.BodyTypePartThing As BodyType
		,t.BodyName
From	@tvTable t
join	cte c
	on	t.BodyType = c.BodyType
join	cte c2
	on	t.BodyType = c2.BodyType
	and	c2.RowNumber = 2
Cross Apply adv.fncDelimitedSplit8K(c.BodyTypePartThing, '-') s1

Edit:

Actually, after seeing Naarasimha's response, it looks like you have three columns, not two.  Makes slightly more sense, and is a lot easier.  You can use any split function you find, and it will work the same.  I use one from Jeff Moden:

Declare @tvTable Table (
	Body		varchar(20)
	,BodyType	int
	,Name		varchar(20)
)

Insert	@tvTable
Values	('123-4567-89', 1, 'xyz')
		,('12-3-45-89', 2, 'abc')

Select	split.Item Body
		,t.BodyType
		,t.[Name]
From	@tvTable t
Cross Apply adv.fncDelimitedSplit8K(t.Body, '-') split

Answer 3

Here is a alternative to do what you asked.

declare @ip as table(body varchar(20), [type] int, name varchar(20))
insert @ip values('123-4567-89', 1, 'xyz'), ('12-3-45-89', 2, 'abc')

select	r.c.value('(./text())[1]', 'varchar(20)') as col1
	   ,[name], [type]
  from	(
			select	cast('<row>' + replace(body, '-', '</row><row>') + '</row>' as xml).query('.') as xmlcol
				   ,[type]
				   ,name
			  from	@ip
		) t
cross apply	t.xmlcol.nodes('/row') r(c)

Answer 4

Hi Raaz,
Here is a unique function which can be used always

CREATE FUNCTION dbo.[SplitText](@String nvarchar(max), @Delimiter nvarchar(10))       
returns @temptable TABLE (txt nvarchar(max))
    WITH SCHEMABINDING      
as       
begin       
    declare @idx int       
    declare @slice nvarchar(max)       
      
    select @idx = LEN(@Delimiter)       
        if len(@String)<LEN(@Delimiter) or @String is null  return       
      
    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       
          
        if(len(@slice)>0)  
            insert into @temptable(txt) values(@slice)       
 
        declare @shift int = len(@String) - @idx-LEN(@Delimiter)
        if @shift<0
            SET @shift=0
        set @String = right(@String,@shift)         
        if len(@String) = 0 break       
    end   
return       
end  

SELECT splitedValues.txt,BodyType,Name
FROM [SourceTable]
CROSS APPLY dbo.[SplitText](Body,'-') AS splitedValues