String function query

i have a table of following structure


Raj Kumar
Pradeep Kumar
Syam Sundhra
Javagal Srinath
Pandit Ravishankar

I need a query with output as follows



Posted On: Thursday 15th of November 2012 08:56:03 PM Total Views:  291
View Complete with Replies

Related Messages:

sql query string help?   (145 Views)
Dear friends... I need a sql query string,.... I have database with names as follows.. ABC ABC DEF FRG FRG FRG FRG DEF ABC Now i need a sql query string to show the data by following way. FRG 4 ABC 3 DEF 2 please help regards Ananda
Calculation errors or MS bug in recursive functions ??   (133 Views)
are there know problems with calculation errors in recursive function in sql server 2000 if not, please point out the error in the following: PHP Code: CREATEFUNCTIONdbo.func_RecVerkoopprijs_basis(@Parentbigint,@Versie_IDbigint)RETURNSdecimal(10,2)ASBEGINDECLARE@Valuedecimal(10,2)IF(@ParentISnull)OR(@ParentIN(SELECTParent_IDFROMOfferte_LijnenWHEREVersie_ID=@Versie_ID))BEGIN IF(@ParentISnull) BEGIN SET@Value=(SELECTSUM(dbo.func_RecVerkoopprijs_basis(Lijn_ID,Versie_ID)*(1-Discount))FROMview_Full_LijnenWHEREParent_IDISnullANDVersie_ID=@Versie_ID) END ELSE BEGIN SET@Value=(SELECTSUM(dbo.func_RecVerkoopprijs_basis(Lijn_ID,Versie_ID)*(1-Discount))FROMview_Full_LijnenWHEREParent_ID=@Parent) ENDENDELSEBEGIN SET@Value=(SELECT(Aantal*Actuele_Verkoopprijs)asprijsFROMview_Full_LijnenWHERELijn_ID=@Parentandoptioneel=0)ENDRETURN@ValueEND select dbo.func_recverkoopprijs_basis(25,5) as p this returns 4193 (wrong because this is supose to be 4220) Reason why: select Actuele_Verkoopprijs * Aantal from view_Full_Lijnen where Parent_ID=25 AND Versie_ID=5 returns: 400.0000 270.0000 250.0000 3300.0000 THEN: select sum(Actuele_Verkoopprijs * Aantal) from view_Full_Lijnen where Parent_ID=25 AND Versie_ID=5 returns: 4220 (this is right) EVEN: select sum(Actuele_Verkoopprijs * Aantal)*(1-(select discount from view_Full_Lijnen where lijn_ID=25 AND Versie_ID=5)) from view_Full_Lijnen where Parent_ID=25 AND Versie_ID=5 returns: 4220 (still right) And yet the salst statement should do the exact same thing the function does anyone any clue
Query string sysntax errors   (144 Views)
I am trying to doa query from php... I am using SQL Server I am trying to get something liek this..where I am usign varaivles for the , = signs These are the variables: varpropertyType = Townhome varPropertyFactor = >, , or
Format function   (108 Views)
I have doubt about sqlserver format function. We have a Format function available in c# nd for formation strings. I just want to know is there any functins available there in sql server 2000 format in c#. Appreciating your resposne. regards, Anish
How to convert hexa string to varbinary?   (119 Views)
hi I have this script to get the default value of a column dynamically ,it works for most columns types except for varbinary that has default hex value, when @t = 0x0343 ,then @v =0x03 note that I had to use nvarchar(4000) at first because INFORMATION_SCHEMA.COLUMNS forces me to do so, can you help me thanks in advance ----------------------------------- create table #temp ( column_name sysname, column_def nvarchar(4000) ) insert into #temp SELECT column_name,column_default FROM INFORMATION_SCHEMA.COLUMNS where table_Name ='table_1' declare @t nvarchar(4000) set @t=(select column_def from #temp where column_name='c_varbin') set @t = replace (@t ,'(','') set @t = replace (@t ,')','') select @t declare @v varbinary set @v = convert ( varbinary,@t) select @v drop table #temp -----------------------------------
Join function   (104 Views)
Folks just wondering if someone could help with this. I have a SQL Join that is giving me a double join here is the SQL Code: SELECT * FROM dbo.FACTS LEFT OUTER JOIN dbo.GRAPHICS as graphics1 ON dbo.FACTS.FACT_GRA_ALIAS = graphics1.GRA_ALIAS LEFT OUTER JOIN dbo.GRAPHICS as graphics2 ON dbo.FACTS.FACT_BKGD_ALIAS = graphics2.GRA_ALIAS LEFT OUTER JOIN dbo.STYLES ON dbo.FACTS.FACT_STYLE_ID = dbo.STYLES.StyleID WHERE dbo.FACTS.FACT_KEY = '100' AND dbo.FACTS.FACT_REGION = 'LEFT' ORDER BY FACTS.FACT_SEQ_NUM Here is some sample data FACT TABLE Code: FACT_BKGD_ALIAS FACT_GRA_ALIAS dm-top-1200.jpg gradient1.gif Null NULL Null NULL Null NULL Null NULL SV2C-1000-1111ZZZA NULL Null NULL SV3C-1500A-1111ZADZ.png NULL dm-bot-1200.jpg Graphic Table Code: dm-top-1200.jpg NULL NULL NULL NULL SV2C-1000-1111ZZZA NULL SV3C-1500A-1111ZADZ.png dm-bot-1200.jpg Problem starts here with second Join Code: NULL gradient1.gif NULL NULL NULL NULL NULL NULL NULL I need the second join that results in gradient1.gif to be in the first join in the graphics result in the second NULL area underneath dm-top-1200.jpg... can anyone tell me where I am going wrong
Syntax error converting datetime from character string   (191 Views)
I urgently need some help I have written a .NET windows service that processes some XML's and updates some fields in an SQL Server 2000 DB. It works ok on my workstation. But at the Client's site it causes an exception with the following error: "Syntax error converting datetime from character string" I heard it might have something to do with regional settings for the workstation and SQL Server Language Settings but im not sure. Could somebody please help with this issure!
Working with dates in SQL strings   (126 Views)
, I wonder if you could help, I am trying to extract records from my database which are BETWEEN two dates. The date field in my databse contains the dates in Short date format, like dd/mm/yyyy. The SQL string I am using is as follows, but it just does not seem to work! It doesnt give any errors, but just doesnt extract any records (which definitely exist). ...FROM Advertisers WHERE Paid = 'yes' AND (dateAdded BETWEEN " & formatDateTime("01/01/05",2) & " AND " & formatDateTime("01/01/06",2) & ")" I am new to working with dates so there is obviously something I am doing wrong ( Your help would be so much appreciated, manyyyy
Performance Issue using left or substring function   (199 Views)
Why are you using a sub-select Why not just use an inner join Code: SELECT A.* FROM Table1 As A INNER JOIN ( SELECT DrID FROM Table2 WHERE (SUBSTRING(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H',' E1A','E1C','E1N','G0A') AND SUBSTRING(Telephone,1,3) IN ('204','250','306','403','416','418','450','506',' 514','519','604','613','705','780','807','819','90 2','905')) ) As B On (A.DrID = B.DrID)
Error using the port in connection string   (178 Views)
Hi all i'm new to this, your assistence will be greatly appriatated :-) i have a connection string (this all in asp ) tCSCst = Provider=SQLOLEDB;Data Source=;Port=1444;Initial Catalog=;User ID=;Password= Set testCaseServerConn = Server.CreateObject("ADODB.Connection") testCaseServerConn.Open tCSCstr but i get the following error An error occured:-2147467259 - Invalid connection string attribute the problem seems to be the port itself, if i use 1433 then it works
ASP connection string on MSSQL database   (114 Views)
hi there! im a newbie on asp and would just like to ask how i could connect my asp page on a ms sql db. the ms sql is located at ==> the username is yosi the password is kadiri database name is records can somebody give me a connection string and sa code on how to display all the data on the database
Help with date functions - dateadd   (148 Views)
. Thought this would be an easy dateadd function but I cant get it to work! Background: Using ASP.Net, VB.NET with SQl server 2000 DB. I have a table i need to query where I want to display all records between a user defined time and a pre defined time i.e Display all records between Now and 30 mins time OR Display all records between [user entered parameter i.e. 15:00] and 1 hour after user defined parameter. I want to have the user select in a drop down the time to query from and the amount of time after that. I hope I have explained that well enough! ALL records in the table are in the format 01/01/2005 12:00:00 i.e. there are different times throughout the day, and all of them are on the same day. On another point is there any way to change this is stored, I only want to bother with time, not dates, at the moment i convert before i display these values. anyway back to the problem, I have tried the following which I thoguht would work: Code: SELECT * FROM tblTimetable where sourcetime between getdate() and dateadd(hh,01,getdate()) Where sourcetime is a datetimefield with a typical value: 01/01/2005 12:00:00 obviously the getdate() function will show the date and time now today, so how can I get this to work
connection string   (114 Views)
i have a problem currently... can seems to set up my connect string to my sql server running on my local computer.. i have tried all kinds of connection string and all dun seems to work. btw my asp codes r tog with my html codes and i have save the file as .asp and i used IE to open the file will i have problem executing the asp codes can some1 pls help me thks in advance
Query data by PART of an asp string value   (103 Views)
I guess this issue is part SQL and part ASP - so hopefully I've posted it to the right forum (tell me if I haven't!). I'm setting up an ASP page where a column (randomKey) in a recordset (rsKeywords) drawn from a MSSQL 2000 table needs to be filtered by part of a value (KeywordString) which is generated at the top of the page. Specifically: if the value of the column randomKey is contained anywhere in KeywordString, then that record needs to be included in the recordset. eg. if the value of KeywordString is "12345 abcde 54321", records where the value in the column randomKey is say 'abcde' or '54321' would be included in the recordset - one where the value is 'zyxwv' wouldn't! I've been able to filter the recordset further after it has loaded on to the page using an INSTR() function. It works OK but obviously this is slower as all rows are loaded and then filtered. Anyone shed any light on this!
SELECT string truncation problem!   (139 Views)
Hi Pals, Need some help. I have big sql which is more than 4000 characters. Basically the sql is formed by UNION operator. For that reason i have declared the datatype as VARCHAR(MAX). and then tried to assign the value to a variable. and when i am trying to print the SQL using PRINT / SELECT , the sql is getting truncated. What i have done is instead of printing i have used SELECT LEN('SELECT ..........'). Then i am getting output as 8000 chars which can easily be accomodated by VARCHAR(MAX) datatype (i.e 2^31 chars.) Why is the sql is getting truncated What could be the problem How could we resolve this problem Do we need to take 2 string variables and do that or else do we have any other solution for this. Basically i am inserting the result fetched by the big SELECT stmt inside my stored procedure. Any Thoughts Any suggestions will be greatly appreciated.
Syntax error converting datetime from character string   (216 Views)
hi I'm having problem with sql query i'll explain u what i'm trying to do when a user sends enquiry for a product it goes to table propertyhittable where hitid the index. if the receiver replies to the enquiry, his reply goes to table mailbox and all subsequent mail correspondence goes to mailbox. now on my Inbox Page i have to dispaly all the enquiries like gmail (each mail clustered with all past correspondence) I used the following query: select HitId,hitid as mailid,Login_Id,Star,Subject,Prop_Code,visitdate, replydate,mailread from propertyhittable where login_id'71727' and deleted=0 and prop_code in (select prop_code from resi_sell where login_id='71727' union select prop_code from comm_sell where login_id='71727' union select prop_code from land_sell where login_id='71727') union (select mb1.Login_Id,mb1.Star,mb1.Subject,mb1.Prop_Code,mb 1.maildate as visitdate, mb1.maildate as replydate,mb1.mailread,mb2.hitid,mb2.mailid from (select hitid,max(mailid) as mailid from mailbox group by hitid) as mb2 inner join mailbox as mb1 on mb1.hitid = mb2.hitid and mb2.mailid = mb1.mailid where login_id71727 and deleted=0 and mb2.hitid in (select hitid from propertyhittable where login_id='71727') ) order by replydate desc i got Server: Msg 241, Level 16, State 1, Line 1 Syntax error converting datetime from character string.
How insert string into dtb   (129 Views)
I use this statement to insert string value in my test dtb is it correct Code: strSql = "UPDATE guestbook SET" _ & " isapproved = '1'" _ & " WHERE gid =" & arr_idcount(c)
Comma delimited string in nvarchar, matching with user id   (201 Views)
Okay heres the deal (I hope I can explain this clearly enough while being as breif as I can): I created a trouble ticket system (ASP site using MS SQL) where the trouble tickets were assigned to only one person, but now I need tickets to be assigned to multiple people. So in my ticket table I changed the assigned_to field to nvarchar and the user id's are comma delimited. On the users home page, when they sign into the site I show currently open tickets assigned to that user. Initially my SQL statement looked like this: strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and t.assigned_to = "& session("user_id")&" and t.status = 'In Progress' and b.business_id = t.business_id" Now as the field type has changed I went looking for something to help me sort thru the comma delimited field to find tickets assigned to the logged in user and am somewhat close with this: strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and (CHARINDEX(',"& session("user_id") &",',t.assigned_to)>0 OR CHARINDEX('"& session("user_id") &",',t.assigned_to)=1 OR CHARINDEX(',"& session("user_id") &"',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by, t.ticket_id" or strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, as [Client] FROM tickets t, users u, business b where u.user_id = "& session("user_id") &" and (PATINDEX('%,"& session("user_id") &",%',t.assigned_to)>0 OR PATINDEX('%"& session("user_id") &",%',t.assigned_to)=1 OR PATINDEX('%,"& session("user_id") &"%',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by, t.ticket_id" neither of which work that well as it will return records that aren't assigned to that user at all and I am nto sure why. I just found charindex() and patindex() and barely know how to use them properly. Please help me as I am at my wits end.
Querystring   (129 Views)
I'm trying to update one column from one table, but I need the information from other table to update it. So my querystring is like this UPDATE InventoryStockTotal, ShoppingCart SET InventoryStockTotal.dblUnitsocated = (InventoryStockTotal.dblUnitsStock + ShoppingCart.Quantity) WHERE InventoryStockTotal.strProductID = tblShoppingCart.strProductID AND ShoppingCart.strCartID = @strCartIDbut I get an error msg. "Incorrect syntax near ',' line 1.anyone can help me