November 5, 2009 at 5:08 am
i need the substring from a column in such a way that i should get the string from 9th to 16th.below qury is throwing error.pls advise
SELECT SUBSTRing
(description,
select charindex ('[', description)from PublishItem,--9
select charindex (']', description)from PublishItem--16
)
FROM PublishItem
WHERE description like '%Doc No%'
November 5, 2009 at 5:15 am
What is the error message?
At a guess, is it complaining if it can't find '[' or ']' in the string as this would cause the substring to fail ?
try
SELECT SUBSTRing
(description,
charindex ('[', description),--9
charindex (']', description)--16
)
FROM PublishItem
WHERE description like '%Doc No%' and description like '%[%' and description like '%]%'
November 5, 2009 at 5:16 am
shanila_minnu (11/5/2009)
i need the substring from a column in such a way that i should get the string from 9th to 16th.below qury is throwing error.pls adviseSELECT SUBSTRing
(description,
select charindex ('[', description)from PublishItem,--9
select charindex (']', description)from PublishItem--16
)
FROM PublishItem
WHERE description like '%Doc No%'
your query doesnt make sense, this is the simplest example
select substring('123456789abcdefghijkl',9,8)
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 5:18 am
Silverfox (11/5/2009)
shanila_minnu (11/5/2009)
i need the substring from a column in such a way that i should get the string from 9th to 16th.below qury is throwing error.pls adviseSELECT SUBSTRing
(description,
select charindex ('[', description)from PublishItem,--9
select charindex (']', description)from PublishItem--16
)
FROM PublishItem
WHERE description like '%Doc No%'
your query doesnt make sense, this is the simplest example
select substring('123456789abcdefghijkl',9,8)
I need the results from entire column
November 5, 2009 at 5:20 am
select substring('12345678[abcdef]hijkl',
CHARINDEX('[','12345678[abcdef]hijkl'),CHARINDEX(']','12345678[abcdef]hijkl'))
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 5:25 am
SELECT SUBSTRing
(description,charindex ('[', description),charindex (']', description)
)
FROM PublishItem
WHERE description like '%Doc No%'
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 5:35 am
sorry read the code wrong this shoud work;
SELECT SUBSTRing
(description,
charindex ('[', description) + 1,
charindex ( ']',description ) - charindex ('[', description) -1
)
FROM PublishItem
WHERE description like '%Doc No%' and description like '%[%' and description like '%]%'
November 5, 2009 at 5:39 am
steveb. (11/5/2009)
sorry read the code wrong this shoud work;
SELECT SUBSTRing
(description,
charindex ('[', description) + 1,
charindex ( ']',description ) - charindex ('[', 'he[lff]lo') -1
)
FROM PublishItem
WHERE description like '%Doc No%' and description like '%[%' and description like '%]%'
Where was charindex ('[', 'he[lff]lo') -1 in the original code
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 5:44 am
It was not in the original code, I just assumed that the poster was looking to extract the string between '[' and ']'
But I guess you know what they say about assumptions..
November 5, 2009 at 5:47 am
steveb. (11/5/2009)
It was not in the original code, I just assumed that the poster was looking to extract the string between '[' and ']'But I guess you know what they say about assumptions..
ROFLMAO, yep is the mother of all ******, but you know that already ๐
I just used the op's original query and made the change, the original query doesnt work due to having the select and from in the column list.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 6:03 am
That's true,
Sorry I just re-read my code and worked out your were picking up on my 'Test' code that I had left in there accidently. I have edited my post.
Must be time for lunch...
November 6, 2009 at 7:51 am
Try this code to select the text between '[' and ']' in a column
SELECT SubString
(description,
charindex ('[', description) +1,
charindex ( ']',description) - (charindex ('[', description) +1)
)
FROM PUBLISHITEM
WHERE DESCRIPTION LIKE '%Doc No%'
November 7, 2009 at 3:45 am
Try This
SELECT SubString
( Description,
CharIndex ('[', Description) +1,
CharIndex (']', Description) - ( CharIndex('[', Description) +1)
)
FROM PublishItem
WHERE Description Like '%Doc No%' and Description Like '%[[%]%'
Regards,
ัฮนเนโโะฝััะฝ
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply