• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

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
0
carlosmonte
Asked:
carlosmonte
  • 7
  • 7
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Aneesh RetnakaranDatabase 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
 
carlosmonteAuthor Commented:
not possible?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
check my last post
0
 
carlosmonteAuthor Commented:
yes it worked. Thanks again
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now