To break a string into several parts

SQL 2000

declare @a varchar(100)
set @a = '6125.349.5601.278.90.black.blue'

The variable @a hold seven different values seperated by '.'
I have a table where a column holds data whoose pattern is exactly like above.
Now I want to break the abve value into seven different columns.
Like
value1=6125
value2=349
value2=5601
value4=278
value5=90
value6=black
value7=blue

All the values should be shown in one row.
Is it possible to do this without using function?
Is it possible to do through select statement?

I want to update the table having columns value1,value2, value3,value4,value5,value6,value7

MTNN

MehramAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
you can do this without a function, but requires a lot of coding  

declare @a nvarchar(4000)
declare  @tbl table (s varchar(1000))
DECLARE @i int ,@j int
SELECT       @i = 1
set @a = '6125.349.5601.278.90.black.blue'


WHILE @i <= len(@a)
BEGIN
      SELECT      @j = charindex('.', @a, @i)
      if @j = 0
      BEGIN
            SELECT      @j = len(@a) + 1
      end
      INSERT      @tbl SELECT substring(@a, @i, @j - @i)
      SELECT      @i = @j +1
END

SELECT * from @tbl
0
 
Pratima PharandeConnect With a Mentor Commented:
declare @sInputList varchar(100)
set @sInputList = '6125.349.5601.278.90.black.blue'
DECLARE @Item Varchar(8000)
declare @Delimiter varchar(1)
set @Delimiter = '.'

WHILE CHARINDEX('.',@sInputList,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0
)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList))))


IF LEN(@Item) > 0
print @Item
end
print @sInputList


try this here you will get the seperateditem ..
now update it  @Item to your table & for last itme @sInputList
0
 
MehramAuthor Commented:
Hi experts

I am getting the result in the shape of array.
I am having
6125
349
5601
278
90
black
blue
into single variable/column.

I want to have like
@col1=6125.@colb=349,@clc=5601, @coldd=278 ............  all in one row
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Pratima PharandeCommented:
declare @sInputList varchar(100)
set @sInputList = '6125.349.5601.278.90.black.blue'
DECLARE @Item Varchar(8000)
declare @Delimiter varchar(1)
declare @cnt int
set @Delimiter = '.'
set @cnt = 1
WHILE CHARINDEX('.',@sInputList,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0
)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList))))


IF LEN(@Item) > 0
print  '@col' + convert(varchar(1),@cnt)+ '=' + @Item
set @cnt = @cnt + 1
end
print  '@col' + convert(varchar(1),@cnt)+ '=' + @sInputList
0
 
perezjosConnect With a Mentor Commented:
0
 
ee_rleeCommented:
try this

SELECT * FROM urTable
WHERE (CAST(column1 AS VARCHAR(20)) + '.' +
             CAST(column2 AS VARCHAR(20)) + '.' +
             CAST(column3 AS VARCHAR(20)) + '.' +
             CAST(column4 AS VARCHAR(20)) + '.' +
             CAST(column5 AS VARCHAR(20)) + '.' +
             CAST(column6 AS VARCHAR(20)) + '.' +
             CAST(column7 AS VARCHAR(20))) = @a
0
All Courses

From novice to tech pro — start learning today.