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
LVL 1
carlosmonteAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Like this

declare @str varchar(100)
set @str = '1000#werterq23'
SELECT CASE WHEN LEN (SUBSTRING(@str,
        CHARINDEX('#',@str)+1 ,
        CASE WHEN  CHARINDEX(')',@str)=0 THEN LEN(@Str)+1 ELSE CHARINDEX(')',@str) END
        -CHARINDEX('#',@str)-1) ) = LEN(@Str) THEN NULL
            ELSE SUBSTRING(@str,
        CHARINDEX('#',@str)+1 ,
        CASE WHEN  CHARINDEX(')',@str)=0 THEN LEN(@Str)+1 ELSE CHARINDEX(')',@str) END
        -CHARINDEX('#',@str)-1) END
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can use CHARINDEX('Delimitter',theString) instaed of start position andof course for lenght
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
for example

declare @str varchar(100)
set @str = '1000-wer'
SELECT SUBSTRING(@str,CHARINDEX('-',@str)+1, LEN(@str)-CHARINDEX('-',@str)+1)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
carlosmonteAuthor Commented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @str varchar(100)
set @str = '1000#werterq)23'
SELECT SUBSTRING(@str,CHARINDEX('#',@str)+1, CHARINDEX(')',@str)-CHARINDEX('#',@str)-1)
0
 
carlosmonteAuthor Commented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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)
0
 
carlosmonteAuthor Commented:
seems to be working. I just need to check the output of the data. let you know soon.
thanks
0
 
carlosmonteAuthor Commented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I dont understand correctly.. can you give me a sample , presently what i am doing is i will return the entire string
0
 
carlosmonteAuthor Commented:
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
0
 
carlosmonteAuthor Commented:
not possible?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
check my last post
0
 
carlosmonteAuthor Commented:
yes it worked. Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.