Need to read each character - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to read each character

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-20 : 12:59:32

Hi
I am not able to figure out how to do it ...
In my problem we are passing parameter value multipla server locations as a string like
'\\us-liv-ssis\sqlhub\,\\us-liv-sql3\cus\' which is seperated by the comma.I want to read the string character by charater till the comma and
could store the part of server location into temp table

like this
#temptbl
\\us-liv-ssis\sqlhub\\us-liv-sql3\cus

but i am not able to figure to how to read this string character by charater and store in temptbl.

I need your help if anyone could do this ..Thanks is advance

Sachin.Nand

2937 Posts

Posted - 2010-04-20 : 13:23:17

[code]
declare @str as varchar(40)
select @str='\\us-liv-ssis\sqlhub,\\us-liv-sql3\cus'
SELECT PARSENAME(replace('\\us-liv-ssis\sqlhub,\\us-liv-sql3\cus',',','.'), 1) ,
PARSENAME(replace('\\us-liv-ssis\sqlhub,\\us-liv-sql3\cus',',','.'), 2)
[/code]

PBUH

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-20 : 13:36:37

Thanks for your reply.

But my temp table should contain all the server location in this manner
from the given parameter.

ServerNo SERVERlOCATION
1 \\us-liv-ssis\sqlhub
2 \\us-liv-sql3\cus

and so on

what ever solution you has provide thanks for that

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-20 : 13:46:55

Have a look to the following link:

http://www.sommarskog.se/arrays-in-sql-2005.html

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-20 : 13:56:52

One way to accomplish the task (from the same link given above)

Create FUNCTION dbo.simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (RetValue Varchar(100)) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (RetValue)
VALUES (convert(Varchar(100), substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

declare @str as varchar(40)
select @str='\\us-liv-ssis\sqlhub,\\us-liv-sql3\cus'
Select * into #MyTempTable from dbo.simple_intlist_to_tbl(@str)

select * from #MyTempTable

I suggest you to have look at the link to understand the other methods of accomplishing the task.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-20 : 14:03:41

Hi dear,

Thanks for your mighty help and for the link .I am just going through the link and trying to learn more things..DO u have personal email id so that if i need your guidance i may write something to you..

Heartiest Thanks to you for your help..

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-20 : 14:04:46

quote:
Originally posted by pk_bohra

One way to accomplish the task (from the same link given above)

Create FUNCTION dbo.simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (RetValue Varchar(100)) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (RetValue)
VALUES (convert(Varchar(100), substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

declare @str as varchar(40)
select @str='\\us-liv-ssis\sqlhub,\\us-liv-sql3\cus'
Select * into #MyTempTable from dbo.simple_intlist_to_tbl(@str)

select * from #MyTempTable

I suggest you to have look at the link to understand the other methods of accomplishing the task.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-20 : 14:27:47

Hi Vijay,

Whenever you have any doubt you can post it in this forum. There are many MVP's and senior members who reply in this forum. That way you will get more exposure on SQL.

Regards,
Bohra.

I am here to learn from Masters and help new bees in learning.

Sachin.Nand

2937 Posts

Posted - 2010-04-20 : 14:31:27

Another way of doing it


alter FUNCTION dbo.simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (RetValue Varchar(100)) AS
BEGIN

insert into @tbl
SELECT PARSENAME(replace(@list,',','.'), 1) 
union all
select PARSENAME(replace(@list,',','.'), 2)
union all
select PARSENAME(replace(@list,',','.'), 3)
union all
select PARSENAME(replace(@list,',','.'), 4)
return
end
go
select * from simple_intlist_to_tbl('\\us-liv-ssis\sqlhub,\\us-liv-sql3\cus,\\us-liv-sql3\cus1') where retvalue is not null

PBUH

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-21 : 03:42:11

Thanks to both of you ..it is working fine and temp table having a column name RetVlue with following values

\\us-liv-ssis\sqlhub\file.txt
\\us-liv-sql3\cus
\\us-liv-dwprod1\file3.txt

But Now I am creating a cursor to check the files availability in each
location one by one .If file is exists is first location it will move
to next for file .And if does not contain the file it should not move
to next ..
find the below cursor and please feed your inputs what i am doing wrong

DECLARE @FILENAME VARCHAR(MAX)
DECLARE @File_Exists int
DECLARE CURSOR_FILECHECK cursor
FOR SELECT RetValue FROM #MyTempTable

OPEN CURSOR_FILECHECK

FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC Master.dbo.xp_fileexist @FileName,@File_Exists OUT
IF @File_Exists = 1
--PRINT 'File Found'
FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
ELSE
BREAK

--PRINT 'File Not Found'
END
CLOSE CURSOR_FILECHECK
Deallocate CURSOR_FILECHECK

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 05:26:55

pk_bohra has answered this question here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143305

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources