hello all
Plz can u suggest m how can i find substring like i want to show first 50 characters and i dont want that my last 50th character should end with full word .. word should be complete it may b 53 or 54 th character
for example :
m using :
substring(Remark,0,50) as Remark
and it returns :
this is .................................. boo
but i want
thi is ....................................book.
complete sentense plz suggest me how is this be don in SQL query.
thax alot
hi i am using html checkboxes to let user to select categories in which search has to be done. there are 4 checkboxes for 4 categories. this query i have checked in query anakyzer. this query is working and displaying results.acc to this query user has selected "3 checkboxes" and entered "airports" to search. SELECT * FROM tbl_Arab_Newsletter WHERE (Month(month) = '2' AND year(month) = '2008') and (cat_number = 1 or cat_number = 2 or cat_number = 3) and (story like '%airports%' ) i have written code like this. strSQL = "SELECT * FROM tbl_Arab_Newsletter WHERE (Month(month) = '"& m &"' AND year(month) = '"& y &"')"strSQL = strSQL & " and cat_number = 1" end if if cat2="on" then 'check box 2 is enabled means this conditionstrSQL = strSQL & " and cat_number = 2" end if if cat3="on" then 'check box 3 is enabled means this conditionstrSQL = strSQL & " and cat_number = 3" end if if cat4="on" then 'check box 4 is enabled means this conditionstrSQL = strSQL & " and cat_number = 4" end if strSQL = strSQL & " and (story like '%" & search & "%')" but this statements are not working. how to write the if conditon acc to the query. pls help and if dont mind pls give me the correct code. thank u.
Please suggest correct answer (A,B,C,D) for the following Question
You are developing an application for a worldwide furniture wholesaler. You need to create an inventory table on each of the databases located in New York, Chicago, Paris, London, San Francisco, and Tokyo. In order to accommodate a distributed environment, you must ensure that each row entered into the inventory table is unique across all location. How can you create the inventory table?
A.Use the identity function. At first location use IDENTITY(1,4), at second location use IDENTITY(2,4), and so on. B.Use the identity function. At first location use IDENTITY(1,1), at second location use IDENTITY(100000,1), and so on. C.CREATE TABLE inventory ( Id Uniqueidentifier NOT NULL DEFAULT NEWID(), ItemName Varchar(100) NOT NULL, ItemDescription Varchar(255) NULL, Quantity Int NOT NULL, EntryDate Datetime NOT NULL). D.Use TIMESTAMP data type.
Give the suitable answer for the below question and explain the answer.
1.You have designed the database for a Web site (or online ticketing agency) that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor. For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data. How should you design the cursor?
(A). Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ. (B). Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction. (C). Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction. (D). Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.
hey can anyone suggest me how to write the efficient( a bit faster) stored proc to generate alternative usernames( with logical variations like the one of hotmail ) if provided one is already present in database... :)
INSERT INTO @students SELECT1 , 10 UNION ALL SELECT1 , 11 UNION ALL SELECT2 , 12 UNION ALL SELECT2 , 13 UNION ALL SELECT2 , 14 UNION ALL SELECT3 , 15 UNION ALL SELECT4 , 16
-- query starts here
SELECT t.teacher_id FROM @teachers t LEFT OUTER JOIN @students s ON t.teacher_id = s.teacher_id GROUP BY t.teacher_id HAVING COUNT(s.teacher_id) = ( SELECT TOP 1 COUNT(s.teacher_id) FROM @teachers t LEFT OUTER JOIN @students s ON t.teacher_id = s.teacher_id GROUP BY t.teacher_id ORDER BY COUNT(s.teacher_id) ) -- query ends here
But the problem with this query is, i am using two outer joins on the same query and on the same tables....
If teacher table and student tables have few thousands of records, this query will not perform good....
Please suggest another way of writing the query which can perform well....
I am new in sqlserver UDF, I am written a UDF but it showing error CREATE FUNCTION udf_DayOfWeek(@dtDate DATETIME) RETURNS VARCHAR(10) AS BEGIN DECLARE @rtDayofWeek VARCHAR(10) SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate) WHEN 1 THEN ‘Sunday’ WHEN 2 THEN ‘Monday’ WHEN 3 THEN ‘Tuesday’ WHEN 4 THEN ‘Wednesday’ WHEN 5 THEN ‘Thursday’ WHEN 6 THEN ‘Friday’ WHEN 7 THEN ‘Saturday’ END RETURN (@rtDayofWeek) END GO
Error Incorrect syntax near '‘'. Msg 102, Level 15, State 1, Procedure udf_DayOfWeek, Line 16 Incorrect syntax near 'END'.
Error: 0xC02020A1 at Data Flow Task, Source - mysourcefile [1]: Data conversion failed. The data conversion for column "myBadColumn" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". Error: 0xC020902A at Data Flow Task, Source - mysourcefile [1]: The "output column "myBadColumn" (157)" failed because truncation occurred, and the truncation row disposition on "output column "myBadColumn" (157)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Two questions: 1. How to tell SSIS not to stop for one bad row. Getting the good rows is more important than one bad row.
Hello,First of all - sorry for may be stupid question, but as I am not aWin* administrator and my field is *nix, I am a little bit stuck witha problem, presented to me by one of the customers. He has few windowsboxes with some webfile and (most important) - mssql database. And heasks for a backup. I found some company, which offers us IBM's Tivolisoftware, but my opinion is that they want a little bit too much forit and also, from my *nix experiece, I know there should be manyother products, probably cheaper and better. If not - we will usethat Tivoli. So we need to backup mssql data and some filessomewhere. The best would be on some ftp.Can anyone please suggest a good and simple way of making such kind ofbackup?Many thanks,Anton.
First posting to the group. I have received a lot of valuable info from youguys. Now, an OT question:What's a good tape drive to perform unmanned weekly backups for a WindowsXP Pro box running SQL server 2000?--Joel Farris | AIM: FarrisJoel** Their Web. Your Way. http://getfirefox.com **
I am trying to recreate the same functionality Google has in regards tosuggesting words (not names), when you misspell something it comes upwith suggestions.We have a list of words in the database to match against.I've looked at SOUNDEX but it is not close enough, DIFFERENCE is evenworse.The only way I can get SOUNDEX to be more accurate is withSELECT [word]FROM [tbl_word]WHERE ( SOUNDEX( word ) = SOUNDEX( 'test' ) AND LEN( word) = LEN('test' ) )I've been looking at Regular Expression matching which I reckon wouldprovide more accurate matches. Not sure how that will affectperformance, as we could be talking about 20,000 records.Or also been looking at the Double Metaphone algorithm.Is there something else that I am missing, anyone know what to use in asituation like this?Thanks in advance.
our company is looking for a good training for SQL server 2005. Majority of attendies will be .NET developers, but some will be technicians who need backup, replication, maint., etc. training. All are pretty familiar with sql server and have experience with SQL 2000. So, it should not be for beginners. Intermediate and advance topics.
Whom you can suggest? Do you have experience with them?
Hi all, I have just started using SQL7 and quite dumb at it. Here is my problem
i have tables ip_address and ip_subnets. both contains more than 20,000 records. Though ideally each subnet should correspond to only one ip address it is not so due to SMS inventory and remote clients configurations etc.
As an example If my ip address is 141.151.128.78 I need to select only 141.151.128.64 ( or atleast 141.151.128.*)as the valid subnet. In other words I need to compare upto 3rd octet and only if it matches with ip address then declare that as the valid subnet. Pls note that ip addresses vary for each machine though there will 4 octets, I can't use character positions as the nos in each octet might vary from 1-255.
Any help would be greatly appreciated Pasted here under is the query script I am playing around with charcter poistions which are not working in my favor. Just added to explain my problems in more clearer way select distinct system_data.name0, System_IP_Address_ARR.ip_addresses0, System_IP_subnets_Arr.ip_subnets0, system_disc.client0 from system_Data, System_IP_Address_ARR, system_ip_subnets_arr, system_disc where system_data.machineid = System_IP_Address_ARR.itemkey and system_data.machineid = system_ip_subnets_arr.itemkey and system_data.machineid = system_disc.itemkey and system_disc.client0 = 1 and substring(System_IP_Address_ARR.ip_addresses0,1,10 ) = substring(System_IP_subnets_ARR.ip_subnets0,1,10) and substring(System_IP_Address_ARR.ip_addresses0,10,1 ) = '.' and System_IP_subnets_ARR.ip_subnets0 not in ('11%.%.%.%','12%.%.%.%', '10%.%.%.%' , '10.%.%.%' , '1.%.%.%') order by System_DATA.name0
I need help seperating a name from first name and last name
The field is like this,
last name, first name example Doe, John I need to seperate the last name from the , to the first character and the last name from the , to the last character.
I think I have to use a substring but not sure how tell it to stop and start when it gets to the comma.
I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:
Coulumn1 ------------------------------- SOUTH EL MONTE CA91733617 BOSSIER LA71172 GARDENA CA90249107 MILWAUKEE WI53216 PARIS IL61944 DUQUOIN IL62832 REDWOOD FALLS MN56283 AUBURN ME04210
I tryed this:
use cimpro1 select substring(cust_shipto_addr_l3, 1, 19) as 'City', substring(cust_shipto_addr_l3, 20, 21) as 'State', substring (cust_shipto_addr_l3, 22, 31) as 'Zip' from opcshto
For some reason, when I run the query I get this for State:
State ------------------------- CA91733617 LA71172 CA90249107 WI53216 IL61944 IL62832 MN56283 ME04210
When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.
iam trying to write a string function which will give me the id part of a mail id but iam geting the string along with @ and when iam trying to remove the last char (@) iam getting error
query: select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail))) from lead_details -----> Gives me id along with @
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1)) from lead_details ------------>gives me error "Invalid length parameter passed to the substring function."
But select (CHARINDEX('@', leadassignedtombemail) - 1) from lead_details works and gives me the length of id without counting @
i am trying to get the last name of the customer, but my db has the names stored as (first,middle, last) order in a single field. i am using the statment: ,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
but it only works if the customer does not have a middle name, otherwise it returs the middle+last as the last name. what should i do/ any ideas??? here is my code
select ActPrjMgr ,ActEmpId ,ActEmpName ,ActCustName ,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST ,ActPrjCode ,left(ActPrjType,2) as Status ,ActEmpTaskCode ,left(ActBillingPeriod,11)as ppedate ,left(ActivityDate,11) as actdate ,ActTimevalue from dbo.ACTIVITIES where ActBudCat = 'labor' and ActBillingPeriod = '11/17/2006' and actprjcode <> ' ' and actprjcode is not null --and ActBillingPeriod = @StartDate order by ActPrjMgr ,ActEmpID ,ActEmpTaskCode ,ActivityDate
In my status table I m having field like Actionby (COLUMN NAME) ------------------- TravelDept TravelDept Approver FinanceDept TravelDept(xyz@yahoo.com)
I m having mail id along with TravelDept in some rows. I want to retreive rows containing TravelDept.When i am retreving i wnat to retreive value 'TravelDept' which is having mail id also. i have written one query,but its retreving only the value which is having mail id. i want to retreive TravelDept value with mail id and Only TravelDept values also. my query is: select replace(SUBSTRING(S.Actionby,CHARINDEX('',S.Actionby,1) , CHARINDEX('(',S.Actionby,1)+0 ),')', '') from status S; But this query retreiving only 1 row only insteadof 3 rows. Total 3 rows having TravelDept value.I want to retreive all these 3 rows. please help me.
I have a SQL column that contains something like this:
ORLANDO ,FL. 32803
COCOA , FL. 32922-8617
SATELLITE BCH,FL 32937-3523
TAMPA , FL. 33609-3105
EAU GALLIE ,FL. 32935 I need only the name of the city, dont need FL or the zip code, how can I do a substring statement on sql to get only that? Thanks, Erick
