|
|
hi,
try this out ...
I am giving you the code to fragment the column field as you have described.
Try the code with diffrent strings like '1.1.1.1' ,'1.891.91.21' etc.
I am taking '1000.087.1567.7956' as example.
<----------------------------------->
DECLARE @vStr AS VARCHAR(100);
SET @vStr='1000.087.1567.7956'
--first no.
SELECT SUBSTRING(@vStr,1,CHARINDEX('.', @vStr) - 1)as A
--second no.
SELECT SUBSTRING(@vStr, CHARINDEX('.', @vStr)+1 ,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )-(CHARINDEX('.', @vStr)+1)) as B
--third no.
SELECT SUBSTRING(@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1 ,CHARINDEX('.',@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)- (CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)) as C
--fourth no.
SELECT SUBSTRING(@vStr,CHARINDEX('.',@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)+1,LEN(@vStr)+1-(CHARINDEX('.',@vStr,CHARINDEX('.',@vStr,CHARINDEX('.', @vStr)+1 )+1)+1)) as D
<-------------------------------->
kindly do feel free to ask if you have doubt about it.....
vivek
delhi
vivek
|
|
|
|
|
To back Colin's comments up - we wrote and support a system where it was originally not anticipated that the parts of a certain identifier (carton number, IIRC) would need to be queried for a certain table. A changed requirement made this necessary. The substring operations make it impossible for SQL Server to use an index, so every row must be accessed, which you almost never want to happen unless your table is very small.
Further, something about the query sometimes causes SQL Server to pick a poor execution plan which takes an unacceptable amount of time to complete. Often running sp_updatestats fixes the problem, but I think this is simply because it forces the query to be recompiled.
We're looking at adding extra columns to sort this out.
In terms of what you need to do - preprocess your data before insertion. Almost any client-side language (C#, VB.NET, etc) is far better at string manipulation than SQL is. If you already have data that's in the format you've shown, then doing a one-time update is acceptable.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I'm building an application that uses ODBC. I'm using SQL Server 2005 Express for my test bed, but I would like to be portable to the customer's DBMS if possible.
What are the 'best practices' for creating an empty data base?
Based on what I've read, I'm guessing that, since data base creation is a privileged activity, that I will have to have the user create the data base manually. I'm thinking that, at that point, my application could go ahead and create the empty tables if they aren't present when I open the data base.
The alternative would be to have some kind of SQL script run by the installer to create the empty tables.
Anyone have recommendations or suggestions?
Software Zen: delete this;
|
|
|
|
|
Create a SQL script that can create db and then create all tables etc.
In 2005 Express. Right Click your db -> Tasks -> Generate Scripts...
This will take you to the script Wizard where you can script your entire database.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Thanks for the response.
Frank Kerrigan wrote: In 2005 Express. Right Click your db -> Tasks -> Generate Scripts...
Yeah, I've got that, and will use it for the 'default' installation case where I'm installing SQL Server 2005 Express.
I would like to provide a 'sanitized' version of that script for creating the data base under other DBMS's (like Oracle, MySQL, etc.). I've not found anything specific on MSDN that describes which parts of their script are not ANSI-SQL, which seems to be a sort of a common denominator.
Software Zen: delete this;
|
|
|
|
|
I need to analyze one of the column values during an insert action, alter it and have it insert into the database and I figure an Instead of trigger is the best way to go. But, the table structure that my instead of trigger is on can change so I do not want to hard-code the column names in my trigger. How do I select from inserted and loop through the columns so that I can create another insert statement with those column names and values?
|
|
|
|
|
Okay, this is a question for a coworker.
Table A has two fields: partNumber, weight
Table B has two fields: partNumber weight
Table A has about 1000 records, table B has about 10,000 records.
Table A has the most recent information about weight. What he wants to do is if the part number in table A also exists in table B, update that weight with the weight from table A. (If the part number exists in table b, the weight is outdated.)
How can we do this in sql?
Thanks all!
|
|
|
|
|
|
Basically ignore any records in table B (with a weight) that doesn't have a corresponding record in table A.
Thanks Colin!
|
|
|
|
|
I told the guy this is for that you are from Scotland. He went on to tell me a story that at a previous job a Scotsman (sp?) and another guy apparently got upset over some dresscode changes, so they showed up at work wearing kilts for a week or so.
|
|
|
|
|
|
I pave ya driveway!
|
|
|
|
|
|
Just a weird expression of mine. Two common phrases here when you agree are:
1. I hear ya cluckin' big chicken!
2. I hear ya barkin' big dog!
I worked at Citibank with guy who claims he came up with those phrases and would have a fit when anyone else used them so I came up with new ones:
1. I pave ya driveway!
2. I squeeze ya Charmin!
3. I toast ya streudel!
Just consider it my crazy American personality.
|
|
|
|
|
|
Well, although I do claim to be the originators of them, please feel free to use them! (I'm sure you'll get some funny looks--good times.)
|
|
|
|
|
hi,
using disconnected architecture, i have generated a dataset consisting of three tables on a form which has a datagrid control and specified the data source as the dataset(and not as dataset.tablename).This shows me the table names on click of the "+" icon on the left top corner and on click of the table name, it navigates to the column names of the particular table clicked.How do i display the column types in the datagrid corresponding to the column names?
Could anyone suggest me any alternate way,if any?
thanks
i'll explain my issue more precisely....
basically, the objective is to create a dataset and fill it with three tables,print the entire table name in the dataset, print all the column names and it's type for each table.
And the following is what i have done:
i've dropped a datagrid on my form from toolbox and from server explorer, i have dropped three tables on the form and generated a single dataset(thus, using disconnected architecture).I've specified the datasource as datasetname in the properties of the datagrid.Now, on compiling and executing, i get a "+" icon on the left top corner of datagrid. On clicking this "+", i get display of the three table names and on clicking any table name,i get the all the column names of the corresponding table with no record sets in the datagrid. How could i get the column types for each and every column?
Is there any other method to perform this?
-- modified at 2:10 Friday 30th June, 2006
|
|
|
|
|
|
|
You have not actually asked any question. Do you want to tell us about your database, or do you want help with something?
|
|
|
|
|
This smells a lot like a homework problem, but it's the end of June so there shouldn't be much homework around right now.
You'll need to join the Mark table to the Lesson table to be able to look up the [Lesson Name] in the Lesson table, to include only those results where [Lesson Name] is equal to Lesson2. I'd then use the NOT IN operator to select those students from the Student table which don't appear in that set. In my view, NOT IN is clearer than trying to use some form of outer join and select rows which have a NULL indicating no match. On the other hand, NOT IN only works when you're trying to filter on a single column, if you have a compound key you're forced to use an outer join.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
|
Mike Dimmick wrote: it's the end of June so there shouldn't be much homework around right now.
But there is always summer school.
|
|
|
|
|
Why don't you post a half-hearted attempt at an sql statement, and we will let you know how to fix it.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
But my driveway is made of Tarmac 