substring and charindex
-
Hi My one column field is something like 1.1.1.1 please note it can be any number between 1 to 1000 before each . e.g. upto 1000.1000.1000.1000 1. my question is how can i extract, anything that starts before second '.' and insert into another column with in a same record. 2. how can i extract any number that starts after second '.' and before third '.' please provide me clear direction cheers.
-
Hi My one column field is something like 1.1.1.1 please note it can be any number between 1 to 1000 before each . e.g. upto 1000.1000.1000.1000 1. my question is how can i extract, anything that starts before second '.' and insert into another column with in a same record. 2. how can i extract any number that starts after second '.' and before third '.' please provide me clear direction cheers.
This breaks the first normal form. You should insert the data into the database in the split form in the first place. You can then create a computed column that concatenates the strings back together again. However, if you are insistant on your approach above you might find these string functions[^] useful. As for inserting the data into other columns. You can either do this when you create the row in the first place using INSERT[^] or after the row is initially created using UPDATE[^]
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Hi My one column field is something like 1.1.1.1 please note it can be any number between 1 to 1000 before each . e.g. upto 1000.1000.1000.1000 1. my question is how can i extract, anything that starts before second '.' and insert into another column with in a same record. 2. how can i extract any number that starts after second '.' and before third '.' please provide me clear direction cheers.
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
-
Hi My one column field is something like 1.1.1.1 please note it can be any number between 1 to 1000 before each . e.g. upto 1000.1000.1000.1000 1. my question is how can i extract, anything that starts before second '.' and insert into another column with in a same record. 2. how can i extract any number that starts after second '.' and before third '.' please provide me clear direction cheers.
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