Find the substring in string

Question

Post reply

Find the substring in string

rpyerneni

Old Hand

Points: 377
More actions
July 6, 2009 at 3:12 pm

#135984

I have a string in the database like this...

<>

<><< href="/site/human_meeting.asp?id=32602">>Agenda and Minute<>

<< href="/site/uploadedfiles/Member_Application.pdf">>Member Application<>

I need to retrieve particular text i.e, "32602" from the above string.

Can any one please help me to solve this?

Thank,

Prasad

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ss-457805 SSCertifiable Points: 5872 More actions · Answer 1

ss-457805

SSCertifiable

Points: 5872

July 6, 2009 at 3:22 pm

#1020704

Try this:



select * from tablename where fieldname like '%32062%'

blog: http://sarveshsingh.com Twitter: @sarveshsing

rpyerneni Old Hand Points: 377 More actions · Answer 2

rpyerneni

Old Hand

Points: 377

July 6, 2009 at 3:26 pm

#1020713

Sarvesh,

Thanks for your immediate response. The query which you gave will returns entire column value. But I need only the text "32602".

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

You need to use PATINDEX(). Something like this:



DECLARE @string VARCHAR(MAX)

SET @string = '

<>Agenda and Minute

<>Member Application'

SELECT

    SUBSTRING(@string, PATINDEX('%32602%', @string),5)

Of course, since you already know what you are looking for within the string, why would you need to extract it out? Or do you really want to find the string following the the id passed in the query string? That would be something like this:



DECLARE @string VARCHAR(MAX)

SET @string = '

<>Agenda and Minute

<>Member Application'

SELECT

    SUBSTRING(@string, CHARINDEX('id=', @string) + 3,5)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

s007reddy SSC Veteran Points: 255 More actions · Answer 4

Gere you go ...

create table Test1 (Tname varchar(1000))

insert into Test1 (Tname) values ('<>Agenda and Minute')

select Tname,

Substring( Tname,

CHARINDEX('id=',tname) + Len('">>'), CHARINDEX('">>',tname) - CHARINDEX('id=',tname) - Len('">>'))

from Test1

rpyerneni Old Hand Points: 377 More actions · Answer 5

Jack,

The ID can vary. It may be 5 or 6 or 7 or 8 charecters. I need to get the ID and replace it with another number.

Reddy,

I tried with your statements. I created the table and inserted the values. I am able to retrieve the ID values. But in the Original table, the column we are working is Text type column.

I replaced the Tname with "Content_Content" and executed the query.

select content_content,

Substring( content_content,

CHARINDEX('id=',content_content) + Len('">'), CHARINDEX('">',content_content) - CHARINDEX('id=',content_content) - Len('">'))

from tbl_page_content

It gave the error...

Msg 536, Level 16, State 5, Line 1

Invalid length parameter passed to the SUBSTRING function.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 6

rpyerneni (7/6/2009)

Jack,

The ID can vary. It may be 5 or 6 or 7 or 8 charecters. I need to get the ID and replace it with another number.

With the code you have already been provided you should be able to figure out how to get the correct length.

If the content is in a TEXT column then you will need to do a CONVERT/CAST to varchar(max) in order to use SUBSTRING.

Your error could be because you may not be finding a character using CHARINDEX or because this code is returning a negative number:

CHARINDEX('">',content_content) - CHARINDEX('id=',content_content) - Len('">')

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

WayneS SSC Guru Points: 95405 More actions · Answer 7

This should do it.



declare @test1 table (Tname varchar(1000))

insert into @test1 (Tname) values ('<>Agenda and Minute')

insert into @test1 (Tname) values ('<>Agenda and Minute')

insert into @test1 (Tname) values ('<>Agenda and Minute')

insert into @test1 (Tname) values ('<>Agenda and Minute')

insert into @test1 (Tname) values ('<>Agenda and Minute')

insert into @test1 (Tname) values ('<>Agenda and Minute')

insert into @test1 (Tname) values ('<>Agenda and Minute')

;with CTE AS

(

select [iPos] = CharIndex('id=', Tname), Tname from @test1

)

select [ID] = substring(Tname, iPos+3, CharIndex('"', TName, iPos)-iPos-3)

  from CTE

Above sample code has from 3-8 characters. Code should handle 1-?.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

rpyerneni Old Hand Points: 377 More actions · Answer 8

rpyerneni

Old Hand

Points: 377

July 6, 2009 at 11:22 pm

#1020883

Hi Wayne,

Thank you very much. I tried with your code. It works. But still I have some issue.

I have multiple URLs in one column like

<>Agenda and Minute<>Agenda<>Minute<>

How can get all the IDs from the above content?

rpyerneni Old Hand Points: 377 More actions · Answer 9

Wayne,

I worked on your code. As I told you I have so many links in the content. I splitted the content and inserted the splitted data into a temporary table. On the Temp table, I am running your query. Now I am able to get all the ID values.

Now I need to insert them into another temp table because I need to fetch one-by-one and replace them with another ID.

So, Can you please tell me how to insert them into another temp table?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

rpyerneni (7/7/2009)

Wayne,

I worked on your code. As I told you I have so many links in the content. I splitted the content and inserted the splitted data into a temporary table. On the Temp table, I am running your query. Now I am able to get all the ID values.

Now I need to insert them into another temp table because I need to fetch one-by-one and replace them with another ID.

So, Can you please tell me how to insert them into another temp table?

I'd recommend that you post the code you've developed so far. There are several reasons for this... one, of course, is to answer your latest question in terms of what you've already done. Two is to see if your split is actually good in the performance department.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

terrance.steadman SSCrazy Points: 2097 More actions · Answer 11

Greetings,

Maybe what you could try is a WHILE loop.



DECLARE @StPos  int

DECLARE @StartID  int

DECLARE @StopID  int

DECLARE @DoneFlag  bit

DECLARE @String  varchar(MAX)

DECLARE @ID  varchar(20)

SELECT

  @StPos = 1,

  @DoneFlag = 0,

  @String = '<>Agenda and Minute<>Agenda<>Minute<>'

WHILE @DoneFlag = 0

  BEGIN

    SELECT

      @StartID = CHARINDEX(@String, 'ID=', @StPos)

    IF @StartID > 0

      BEGIN

        SELECT

          @StopID = CHARINDEX(@String, '"', @StartID)

        SELECT

          @ID = SUBSTRING(@String, @StartID, @StopID)

        -- At this point, you can replace the piece of the string with a new value,

        -- or copy to and build a new string.

        SELECT

          @StPos = @StopID + 1

      END

    ELSE

      BEGIN

        SELECT

          @DoneFlag = 1

      END

  END

This will let you iterate through your string until all instances of ID= are found, and replaced.

Have a good day.

Terry Steadman