Split String Value on find "]" new row

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)

Split String Value on find "]" new row

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 03:24:52

Good day, I need help please.

I have a string value: Agent-North West [2F] Agent-North East [2P]
I would like to split the string on find "]" create new row.

output: Agent-North West [2F]
Agent-North East [2P]

Please Help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-02 : 03:43:28

try like this
declare @str1 varchar(max)
set @str1= 'Agent-North West [2F] Agent-North East [2P]Agent-North North [2P]Agent-North South [2P]'

SELECT * FROM (
SELECT SUBSTRING(@str1,charindex(']',@str1,v.number)+1,abs(charindex(']',@str1,charindex(']',@str1,v.number)+1)-charindex(']',@str1,v.number))) as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND substring(']' + @str1, v.number, 1) = ']')s WHERE [VALUE] <> ''

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-02 : 03:45:33

see these links
use fnParseList(',', Col7) function for this
search this function in this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting,delimited,lists

parse values
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 03:58:53

Unfortunately, I'm not winning on both methods.

On the link i can't find the function: fnParseList

Please Assist!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-02 : 04:07:25

here fnParseList

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-02 : 04:08:57

quote:
Originally posted by ismailc

Unfortunately, I'm not winning on both methods.

On the link i can't find the function: fnParseList

Please Assist!

Post what you have tried with first reply

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 04:22:19

Hi,
I'm winning a bit, but I get a space in front of some Values on the new row even when i'm doing a ltrim:
eg: Test1 [1]
Test2 [2]
Test3 [3]
Test4 [4]

declare @str1 varchar(1000)
select @str1 = MAX(v.ControlValue)
from dbo.fcEventArchive as e

SELECT distinct ltrim(rtrim(value)) as Sites FROM (
SELECT SUBSTRING(@str1,charindex(']',@str1,v.number)+1,abs(charindex(']',@str1,charindex(']',@str1,v.number)+1)-charindex(']',@str1,v.number))) as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND substring(']' + @str1, v.number, 1) = ']')s WHERE [VALUE] <> '

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-02 : 04:26:46

You would get space if you use ltrim

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 04:33:11

I don't understand = i het space using ltrim
" test3 [3]" - and i want "test3 [3]"
ltrim(value)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-02 : 04:36:28

[code]
declare @str varchar(max)

set @str= 'Agent-North West [2F] Agent-North East [2P]Agent-North North [2P]Agent-North South [2P]'

select ltrim(Data) + ']'
from fnParseList(']', @str)
where Data <> ''
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 04:44:10

Thank You - I created the function & tried.

but unfortunatley it also returns rows with a space in front

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-02 : 04:46:39

example ?

KH
[spoiler]Time is always against us[/spoiler]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 04:49:22

I'm also struggling to create the stored procedure:

ALTER PROCEDURE [dbo].[FC0_RP_VendorSite]
@ProcessNo int, @str1 varchar(1000)
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS (select * from FC_LIVE.Flowcentric.dbo.fcEventArchive where ProcessID = @ProcessNo)

select @str1 = MAX(v.ControlValue)
from FC_LIVE.Flowcentric.dbo.fcEventArchive as e
where e.ProcessID = @ProcessNo

SELECT distinct ltrim(value) as Sites FROM (
SELECT SUBSTRING(@str1,charindex(']',@str1,v.number)+1,abs(charindex(']',@str1,charindex(']',@str1,v.number)+1)-charindex(']',@str1,v.number))) as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND substring(']' + @str1, v.number, 1) = ']')s WHERE [VALUE] <> ''

Else

select @str1 = MAX(v.ControlValue)
from FC_LIVE.Flowcentric.dbo.fcEventHist as e
where e.ProcessID = @ProcessNo

SELECT distinct ltrim(value) as Sites FROM (
SELECT SUBSTRING(@str1,charindex(']',@str1,v.number)+1,abs(charindex(']',@str1,charindex(']',@str1,v.number)+1)-charindex(']',@str1,v.number))) as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND substring(']' + @str1, v.number, 1) = ']')s WHERE [VALUE] <> ''

END

The If else works when using normal query

But i get the error: Incorrect syntax near the keyword 'Else'

Please Assist!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-02 : 04:50:33

use if()begin
select...........
end
else
begin
select..........
end

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 04:51:51

values reurned:
"test1 [1]"
"test2 [2]"
" test3 [3]"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2009-11-02 : 04:55:40

Thank You :) I got the stored procedure working.

I must only get rid of the space in front of the value