asked on

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

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mehram

ASKER

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

Pratima

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

SOLUTION

perezjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ee_rlee

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