substring with defined delimiters
hi experts,
i'm looking for an SQL function where i can extract a string but instead of start and end position to set the limits of the substring I need it with specific characters.
This would be similar to extractString in Crystal Reports.
Any ideas,
Thanks
cm
i'm looking for an SQL function where i can extract a string but instead of start and end position to set the limits of the substring I need it with specific characters.
This would be similar to extractString in Crystal Reports.
Any ideas,
Thanks
cm
Aneesh
you can use CHARINDEX('Delimitter',the
for example
declare @str varchar(100)
set @str = '1000-wer'
SELECT SUBSTRING(@str,CHARINDEX('
declare @str varchar(100)
set @str = '1000-wer'
SELECT SUBSTRING(@str,CHARINDEX('
ASKER
The delimiters need to be pound "#" and closed parethesis ")"
can you show me how to do this?
Length is not a factor, i will never know the length of the substring
can you show me how to do this?
Length is not a factor, i will never know the length of the substring
declare @str varchar(100)
set @str = '1000#werterq)23'
SELECT SUBSTRING(@str,CHARINDEX('
set @str = '1000#werterq)23'
SELECT SUBSTRING(@str,CHARINDEX('
ASKER
I get this error message
Invalid length parameter passed to the substring function
some fields that i'm getting the substring from do not have a "#" or ")"
this needs to be taken in account
Invalid length parameter passed to the substring function
some fields that i'm getting the substring from do not have a "#" or ")"
this needs to be taken in account
OK , try this
declare @str varchar(100)
set @str = '1000#werterq23'
SELECT SUBSTRING(@str,
CHARINDEX('#',@str)+1 ,
CASE WHEN CHARINDEX(')',@str)=0 THEN LEN(@Str)+1 ELSE CHARINDEX(')',@str) END
-CHARINDEX('#',@str)-1)
declare @str varchar(100)
set @str = '1000#werterq23'
SELECT SUBSTRING(@str,
CHARINDEX('#',@str)+1 ,
CASE WHEN CHARINDEX(')',@str)=0 THEN LEN(@Str)+1 ELSE CHARINDEX(')',@str) END
-CHARINDEX('#',@str)-1)
ASKER
seems to be working. I just need to check the output of the data. let you know soon.
thanks
thanks
ASKER
one last thing
if there is no substring this function gives me the entire @str variable again.
I need it to be NULL or empty (preferred NULL) if there is no substring.
if there is no substring this function gives me the entire @str variable again.
I need it to be NULL or empty (preferred NULL) if there is no substring.
I dont understand correctly.. can you give me a sample , presently what i am doing is i will return the entire string
ASKER
correct, it returns the entire string from @str because there is no substring with the delimiters we established.
instead though if there is no substring between those delimiters, the select should return empty string or NULL
let me know if this is clearer
instead though if there is no substring between those delimiters, the select should return empty string or NULL
let me know if this is clearer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
not possible?
check my last post
ASKER
yes it worked. Thanks again