Query for substring to get some particular value in a string (Get querystring value by sql )

I have a column name URL in Table1 with data like

  <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a>           
<a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> 

i need to grab the news_media_i value by sql query 

 Please any one can help me to get that particular value from string using substring or any other suggestion

 Thank you in advance
 



Surendra
If this helps mark it as answer
0
surendra_kla
1/28/2008 2:22:21 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
1062 Views

Similar Articles

[PageSpeed] 13

surendra_kla:
i need to grab the news_media_i value by sql query
 

What do you really mean? You can retrieve it with Request.QueryString["news_media_i"] in C# or Request.QueryString("news_media_i") in VB.NET


__
Please mark post as answer if it helps, if not come back and ask again!
0
millet
1/28/2008 3:34:22 PM

surendra_kla:

I have a column name URL in Table1 with data like

  <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a>           
<a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> 

i need to grab the news_media_i value by sql query 

 Please any one can help me to get that particular value from string using substring or any other suggestion

 Thank you in advance
 


 

Hi surendra_kla,

Of-course you can get a particular value from string using string function in sql.

Try the database function I have created for you.

 

ALTER FUNCTION dbo.GetNewsMediaID 
	(
		@paramNewsMediaLink VARCHAR(MAX)
	)
RETURNS INT
AS
	BEGIN
	
		IF CHARINDEX('news_media_i=', @paramNewsMediaLink) > 0
		BEGIN 
			DECLARE @TempLink AS VARCHAR(MAX)
			SET @TempLink = RIGHT(@paramNewsMediaLink, (LEN(@paramNewsMediaLink) - CHARINDEX('news_media_i=', @paramNewsMediaLink)) + 1)
		
			DECLARE @NewsMediaQryString AS VARCHAR(MAX)
			SET @NewsMediaQryString = LEFT(@TempLink, CHARINDEX('"', @TempLink)-1)

			DECLARE @NewsMediaID AS INT
			SET @NewsMediaID = CAST(REPLACE(@NewsMediaQryString, 'news_media_i=','') AS INT)
			RETURN @NewsMediaID 
		END
		
			RETURN 0
		
			
	
	END
  

You can call this function by using the following sql query

SELECT dbo.GetNewsMediaID([URL]) AS [NewsMediaID] FROM [Table1] 

  

Some points to note:

This function will return zero, if there is no querystring named news_media_i=

With all do respect, I don't think that storing a url directly to table is not a good idea.

It's a bad database design I guess. If you haven't enough reasons to back your point its better to add additional field in your table like "new_media_id" and separate your id from the link before insert.

 Try this and let me know your response.

Regards,


Regards,
Sandeep Kumar

MCPD - Web Developer
MCTS - .NET Framework 2.0: Web Applications
--------------------------------------------------
Software Associates

0
ASP
1/28/2008 5:21:22 PM

hi,

Please change

ALTER FUNCTION dbo.GetNewsMediaID

to

CREATE FUNCTION dbo.GetNewsMediaID

Regards,


Regards,
Sandeep Kumar

MCPD - Web Developer
MCTS - .NET Framework 2.0: Web Applications
--------------------------------------------------
Software Associates

0
ASP
1/28/2008 5:23:46 PM

Thanq you,

 this is what i am looking for ...

I writn my own function based on your function:

 

ALTER FUNCTION [dbo].[FncGetMediaId]
(
    @content varchar(max)
)
RETURNS int
AS
BEGIN
    declare @SIn as int
    declare @len as int
    declare @MediaId as int

    set @SIn  = (select charindex('=',@content,15))+1
    set @len  = (select charindex('"',@content,15)) - @SIn

    set @MediaId = (select substring(@content, @SIn, @len))

    return(@MediaId)
END
 
 

:-) 


 


Surendra
If this helps mark it as answer
0
surendra_kla
1/29/2008 10:23:13 AM
Reply:

Similar Artilces:

Getting value from a SQL String?
Hi all,       I am trying to get the value from a SQL string and store it in a variable but i'm guessing DLookup is not used in VBScript/ASP as it is with Access/VBA which is my background so the synax below is of course not working ! strvalue = dlookup("PicInsert") FROM tblCon WHERE "Name" = Request.Form("contributor") How would I go about doping this ? Thanks in advance,Mitch... set rs = conn.Execute("Select PicInsert From tblCon WHERE Name = '" & Request.Form("contributor") & "'&q...

getting value of sql query
I have a query that returns 1 result with 2 columns   how do i get the value of the row without doing a for each statement    Dim MyConn As New SqlConnection Dim MyAdapter As New SqlDataAdapterDim ds As New DataSet Dim tbl As New DataTableDim drRow As DataRow MyConn.ConnectionString = strconn MyConn.Open() MyAdapterClient.SelectCommand = New SqlCommand("select * from table", MyConn) MyAdapterClient.Fill(ds) MyConn.Close() tbl = ds.Tables(0)   get value of column1, column 2 with out for eachFor Each drRow In tbl.Rows   Next  ...

SQL Query Get Column Value
Hi,   I have a Country table with the following structure  CountryID            Country  1                         USA  2                         UK  3                   &nb...

How to get the value of a SQL query before binding it to DataGrid?
I am using C#.Net, Visual web Developer, SQL server 2000. I have a SQL query which I am binding it to a DataGrid. SQL : "SELECT ord_number, ord_ID, ord_split, ord_Name, ETD_Date, OSP_FSD FROM ORDERS" In My DataGrid I have a dynamic databound column. I am able to bind one column to this databound column using following code. BoundColumn ETDDate = new BoundColumn(); ETDDate.HeaderText = "ETD Date"; ETDDate.DataField = "OSP_FSD"; mygrid2.Columns.AddAt(ETDDate); but now I want to bind this databound column based on the following criteria to two different database columns. if(ord_split = 1) { ETD...

Getting a value from a SQL table displaying a specific page based on that value
May seem to newbie but here goes:I have a login page.  For the login I run a stored procedure with 4 return values, 0 - being valid, 1 - username exists already, 2 - username is incorrect3 - password is incorrectThis all works fine.  But from that I have the default page when the login credentials are correct.  The issue is I need to display a specific default page based on their location id from the member table which I already run a sp against for the login.  I have set up an .ascx page with a datagrid to display the results which works when I run the query for a h...

Get info from PropertyValueString
Hi All, I have inherited a project written by someone who was a much better programmer than myself and would appreciate some help in the right direction as I am way out of my depth. The problem is I need info about the logged in users profile in the asp_netProfile table which contains a field "PropertyValueString" with xml (nText field) in that looks like this:  <?xml version="1.0" encoding="utf-16"?><ProfileObject xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"...

HELP! Need a SIMPLE way to update a SQL record, and GET a SQL value.
 I am new to asp.net, but I have to believe there is a simple way to write a function that will set a value in a sql database to a variable in the code (Im writing in c#) and also GET a value and set it to a variable.How can I do something as simple as String tempString = 'Fred'; UPDATE test1SET fname =temStringWHERE lname='Flintstone'; I also want to do somtheing equally as simple after that is done: Set a variable to the result of the update.For example String tempFName;tempFName = (SELECT fname FROM Users WHERE lname = 'Flintstone') I get t...

Need help getting a return value in SQL Query Analyzer
This has got to be a simple one, but I could not find out how I have this simple proc for test purposes only...   ALTER    PROCEDURE dbo.RotoTest( @strSSN VARCHAR(11), @blnUseACHDate BIT = 0,  @intInvestorId int = 0)ASreturn 5   Thats right it does nothing but return 5 and thats because I removed all the real code to simplify my question. When I hit this proc in SQL Query Analyzer.. exec RotoTest '123-45-6789', 0, 1     No return value is displayed in Query Analyzer. When I try something like this exec RotoTest ...

SQL QUERY replace NULL value in a row with a value from the previous known value
Hi!! I have a table with no unique ID, I created an Id with ROW_NUMBER () OVER (ORDER BY column asc) rank and created a stored procedure with the rank and table. In this stored procedure I want to create an extra column that checks if the value from another columns contains value = 100 rank,Ifnummer,followingnr,productnr testing 2313,'6497269','001' ,412 , NULL 2314,'6497269','002' ,413 , NULL 2315,'6497269','003' ,100 , yes 2316,'6497269','004' ,430 ,NULL ...

Get Bind value not String value
 Hello,Why do I get the string value and not the bind value?...<asp:ListView ID="ListView1" runat="server">    <LayoutTemplate>            <asp:PlaceHolder ID="itemPlaceHolder" runat="server"></asp:PlaceHolder>    </LayoutTemplate><ItemTemplate><%#getMyValue(Eval("approved"))%></ItemTemplate></asp:ListView>    Function getMyValue (ByVal x As String) As String    ‘if my ap...

get a string value from a sql text data type in web services?
hello, Im building an API for a CRM application that works with sql 2000... one of the fields saves rtf to text data type in sql, and i'm trying to extract this data... however, when i try to conver to string in .net, and try to print out this information .net interprets the rtf formatting as html information can anyone suggest a method of returning just the string value of the data stored in sql? i have tried casting the data type to varchar in my select string, but the formatting is retained... thanks! i think the issue here isn't so much "'stripping" the HTML from the text returned ...

How do I get the Identity field value after a sql insert so the value can be included in the email sent upon submit?
I have  a form that gathers a bunch of data, inserts into a sql database via a stored procedure, and then emails a user some details.  How can I include the value of the unique identifier for the row just inserted into sql in the email?    Here's my code behind.. and I don't know how/where to query the db to get the value and insert it... Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click InsertConflictSearch.InsertParameters("SearchStatusID").DefaultValue = "1"InsertConflictSearch.Insert...

Get values of SQL arguments
Hi All, I am using PB 7.0.3 with ASE 12.5 for Linux. I want to get the values of SQL arguments, how can I do? For example: select * from city where country = :country_code I want to know the value country_code now, how? Pls advise. Thanks, DS Debug? On 9/3/04 8:59 PM, in article 41393f41$1@forums-2-dub, "DS" <danielso_hk@hotmail.com> wrote: > Hi All, > > I am using PB 7.0.3 with ASE 12.5 for Linux. > > I want to get the values of SQL arguments, how can I do? For example: > select * from city where country = :country_code >...

Getting Sql Variable value
Hi,  Looking for a sample in C# (ASP2.0 vwd) of getting a  SqlDataSource Variable (@uNameID) into a string Variable (string uNameID)   Any help would be appreciated!   <asp:SqlDataSource ID="SqlDataSource3" runat="server" OnSelected="SqlDataSource3_Selected"  ConnectionString="<%$ ConnectionStrings:testExpressConnectionString %>"                    SelectCommand="SELECT    userName, userPWD  FROM  user_test where userName=@userName "&g...

Web resources about - Query for substring to get some particular value in a string (Get querystring value by sql ) - asp.net.sql-datasource

Platform Updates: Operation Developer Love
Since last Wednesday's post , we announced all the events in april we will be attending. Defaulting new apps to sandbox mode Starting today, ...

Oxford Professor of Poetry - Wikipedia, the free encyclopedia
(£4,695 as of 2005) plus £40 in travel expenses for each Creweian Oration . The Professor of Poetry delivers three lectures each year. In addition, ...

Z- blog跨站脚本攻击漏洞 - 80sec
Z- blog跨站脚本攻击漏洞

Check Websites For Bad Links And Invalid References In Mac - Integrity
One thing which should be considered to maintain the integrity of a website is that it must not have too many broken links, as they don't only ...

Smart cache-busting for your WordPress stylesheet
Is this a frustration you're familiar with? If you're like me you mess around with your Wordpress CSS stylesheet fairly ...

Getting Schooled: An America Tonight special series - Al Jazeera America
On air and online, America Tonight explores in depth some of the biggest hurdles keeping kids from a good education

AlloyUI
1.7.0 YUI 3.7.3 APIs Classes Modules Everything A.DataType.DateMath A.HTML5 A.io A.Node A.NodeList A.Plugin.IO Anim App App.Base App.Content ...

Serverside stylesheet switcher
Stylesheet switcher using jQuery This page can be used so that the stylesheet switcher degrades gracefully where Javascript is disabled or the ...

DEA Educational Foundation Gift Shop - Apparel, Office Supplies, Desktop, T-Shirts, Polo Shirts, Hats ...
0 Items My Account - Order Status A Problem Occurred: Invalid input or characters from QueryString. Invalid input or characters from Querystring. ...

What is the limit on QueryString / GET / URL parameters? Finding content
What is the limit on QueryString / GET / URL parameters?

Resources last updated: 11/22/2015 3:09:18 AM