|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
extracting datadata examples: source field PHYSICAL (exp:1/2/2006) PHYSICAL (exp: 2/11/2006) PHYSICAL (exp: 11/12/2006) I need to just extract the date, not the 'exp:' between the ( ) and insert into a date field called exp_date. results should be 1/2/2006, 2/11/2006 and 11/12/2006 Could you show me the syntax or example to do this? data example: LECT COPACKER_USER_physical_inventory.id, COPACKER_USER_physical_inventory.pir_id, COPACKER_USER_physical_inventory.exp_date, COPACKER_USER_physical_inventory.notes, Mid([notes],15,10) AS expr2 FROM COPACKER_USER_physical_inventory WHERE ((Left([notes],14)='PHYSICAL (exp:')); maybe a kludge like this?
create table #temp (a varchar(50)) insert into #temp values('PHYSICAL (exp:1/2/2006)') insert into #temp values('PHYSICAL (exp: 2/11/2006)') insert into #temp values('PHYSICAL (exp: 11/12/2006)') select cast(replace(replace(a,'PHYSICAL (exp:',''),')','') as datetime) from #temp drop table #temp Thank you - if i wanted to insert every entry from the notes field what
modification do I need to what you sent? Show quote "Omnibuzz" wrote: > maybe a kludge like this? > > create table #temp (a varchar(50)) > insert into #temp values('PHYSICAL (exp:1/2/2006)') > insert into #temp values('PHYSICAL (exp: 2/11/2006)') > insert into #temp values('PHYSICAL (exp: 11/12/2006)') > > select cast(replace(replace(a,'PHYSICAL (exp:',''),')','') as datetime) from > #temp > > drop table #temp > > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > > In addition to Omnibuzz's suggestion, this 'should' work even if there are
other lead-in words instead of 'Physical'. SELECT substring(@Text, patindex('%exp: %', @text) + 5, (len(@text) - (patindex('%exp: %', @text) + 5))) -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "john d" <jo***@discussions.microsoft.com> wrote in message news:4BF4B7A2-EE20-4782-9B72-E26E01E6DB75@microsoft.com... >I have the following char field called NOTES > data examples: > > source field > > PHYSICAL (exp:1/2/2006) > PHYSICAL (exp: 2/11/2006) > PHYSICAL (exp: 11/12/2006) > > I need to just extract the date, not the 'exp:' between the ( ) and > insert > into a date field called exp_date. > > results should be 1/2/2006, 2/11/2006 and 11/12/2006 > > Could you show me the syntax or example to do this? > > data example: LECT COPACKER_USER_physical_inventory.id, > COPACKER_USER_physical_inventory.pir_id, > COPACKER_USER_physical_inventory.exp_date, > COPACKER_USER_physical_inventory.notes, Mid([notes],15,10) AS expr2 > FROM COPACKER_USER_physical_inventory > WHERE ((Left([notes],14)='PHYSICAL (exp:')); Try
select substring('PHYSICAL (exp:1/2/2006)', charindex('exp:', 'PHYSICAL (exp:1/2/2006)')+4, len('PHYSICAL (exp:1/2/2006)') - (charindex('exp:', 'PHYSICAL (exp:1/2/2006)')) - 4) Of course, you should put your string into a variable first. Show quote "john d" <jo***@discussions.microsoft.com> wrote in message news:4BF4B7A2-EE20-4782-9B72-E26E01E6DB75@microsoft.com... >I have the following char field called NOTES > data examples: > > source field > > PHYSICAL (exp:1/2/2006) > PHYSICAL (exp: 2/11/2006) > PHYSICAL (exp: 11/12/2006) > > I need to just extract the date, not the 'exp:' between the ( ) and > insert > into a date field called exp_date. > > results should be 1/2/2006, 2/11/2006 and 11/12/2006 > > Could you show me the syntax or example to do this? > > data example: LECT COPACKER_USER_physical_inventory.id, > COPACKER_USER_physical_inventory.pir_id, > COPACKER_USER_physical_inventory.exp_date, > COPACKER_USER_physical_inventory.notes, Mid([notes],15,10) AS expr2 > FROM COPACKER_USER_physical_inventory > WHERE ((Left([notes],14)='PHYSICAL (exp:')); With this example how can I set the @datestring = whatever is in the Notes
field of the COPACKER_USER.physical_inventory table? Declare @datestring varchar(40) set @datestring = t1.notes select substring(@datestring, charindex('exp:', @datestring)+4, len(@datestring) - (charindex('exp:', @datestring)) - 4) from COPACKER_USER.physical_inventory t1 I get the following error: Server: Msg 107, Level 16, State 2, Line 2 The column prefix 't1' does not match with a table name or alias name used in the query. How about trying something like:
UPDATE COPACKER_USER.physical_inventory SET Exp_Date = substring(Notes, patindex('%exp: %', Notes) + 5, (len(Notes) - (patindex('%exp: %', Notes) + 5))) WHERE Notes LIKE '%exp: %' -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "john d" <jo***@discussions.microsoft.com> wrote in message news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... > With this example how can I set the @datestring = whatever is in the Notes > field of the COPACKER_USER.physical_inventory table? > > > > Declare @datestring varchar(40) > set @datestring = t1.notes > select substring(@datestring, charindex('exp:', @datestring)+4, > len(@datestring) - (charindex('exp:', > @datestring)) - 4) > from COPACKER_USER.physical_inventory t1 > > > I get the following error: > > Server: Msg 107, Level 16, State 2, Line 2 > The column prefix 't1' does not match with a table name or alias name used > in the query. > Arnie,
getting real close - this is the error that came back "Server: Msg 8116, Level 16, State 2, Line 1 Argument data type text is invalid for argument 1 of len function." Show quote "Arnie Rowland" wrote: > How about trying something like: > > UPDATE COPACKER_USER.physical_inventory > SET Exp_Date = substring(Notes, patindex('%exp: %', Notes) + 5, (len(Notes) - (patindex('%exp: %', Notes) + 5))) > WHERE Notes LIKE '%exp: %' > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "john d" <jo***@discussions.microsoft.com> wrote in message news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... > > With this example how can I set the @datestring = whatever is in the Notes > > field of the COPACKER_USER.physical_inventory table? > > > > > > > > Declare @datestring varchar(40) > > set @datestring = t1.notes > > select substring(@datestring, charindex('exp:', @datestring)+4, > > len(@datestring) - (charindex('exp:', > > @datestring)) - 4) > > from COPACKER_USER.physical_inventory t1 > > > > > > I get the following error: > > > > Server: Msg 107, Level 16, State 2, Line 2 > > The column prefix 't1' does not match with a table name or alias name used > > in the query Apparently, the Notes field is a text/ntext datatype.
It would have been good to know that earlier. It would have saved several folks some time trying to find a solution for you. Most of the suggestions offered so far will not work on a text/ntext datatype. A couple of choices come to mind. 1. ALTER the table to change the Notes field to varchar(), select an appropriate lenght. 2. In the UPDATE query below, cast each instance of the Notes column as varchar() 3. If you cannot change the Notes datatype in the table, and if the dataset is not very 'large', load it into a #Temp table, changing the field datatype in the #Temp table and update with a join. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "john d" <jo***@discussions.microsoft.com> wrote in message news:23DA9517-D4AD-439B-A1DE-7FAD35E7FEF7@microsoft.com... > Arnie, > getting real close - this is the error that came back > > "Server: Msg 8116, Level 16, State 2, Line 1 > Argument data type text is invalid for argument 1 of len function." > > "Arnie Rowland" wrote: > >> How about trying something like: >> >> UPDATE COPACKER_USER.physical_inventory >> SET Exp_Date = substring(Notes, patindex('%exp: %', Notes) + 5, >> (len(Notes) - (patindex('%exp: %', Notes) + 5))) >> WHERE Notes LIKE '%exp: %' >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "john d" <jo***@discussions.microsoft.com> wrote in message >> news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... >> > With this example how can I set the @datestring = whatever is in the >> > Notes >> > field of the COPACKER_USER.physical_inventory table? >> > >> > >> > >> > Declare @datestring varchar(40) >> > set @datestring = t1.notes >> > select substring(@datestring, charindex('exp:', @datestring)+4, >> > len(@datestring) - (charindex('exp:', >> > @datestring)) - 4) >> > from COPACKER_USER.physical_inventory t1 >> > >> > >> > I get the following error: >> > >> > Server: Msg 107, Level 16, State 2, Line 2 >> > The column prefix 't1' does not match with a table name or alias name >> > used >> > in the query Thank you for your help - I did not know at the time I submitted the question
that the Notes field was a text/ntext datatype - next time I will ensure I provide that type of information. I do thank everyone that worked on this. Show quote "Arnie Rowland" wrote: > Apparently, the Notes field is a text/ntext datatype. > > It would have been good to know that earlier. It would have saved several > folks some time trying to find a solution for you. > > Most of the suggestions offered so far will not work on a text/ntext > datatype. > > A couple of choices come to mind. > > 1. ALTER the table to change the Notes field to varchar(), select an > appropriate lenght. > 2. In the UPDATE query below, cast each instance of the Notes column as > varchar() > 3. If you cannot change the Notes datatype in the table, and if the dataset > is not very 'large', load it into a #Temp table, changing the field datatype > in the #Temp table and update with a join. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "john d" <jo***@discussions.microsoft.com> wrote in message > news:23DA9517-D4AD-439B-A1DE-7FAD35E7FEF7@microsoft.com... > > Arnie, > > getting real close - this is the error that came back > > > > "Server: Msg 8116, Level 16, State 2, Line 1 > > Argument data type text is invalid for argument 1 of len function." > > > > "Arnie Rowland" wrote: > > > >> How about trying something like: > >> > >> UPDATE COPACKER_USER.physical_inventory > >> SET Exp_Date = substring(Notes, patindex('%exp: %', Notes) + 5, > >> (len(Notes) - (patindex('%exp: %', Notes) + 5))) > >> WHERE Notes LIKE '%exp: %' > >> > >> -- > >> Arnie Rowland, Ph.D. > >> Westwood Consulting, Inc > >> > >> Most good judgment comes from experience. > >> Most experience comes from bad judgment. > >> - Anonymous > >> > >> > >> "john d" <jo***@discussions.microsoft.com> wrote in message > >> news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... > >> > With this example how can I set the @datestring = whatever is in the > >> > Notes > >> > field of the COPACKER_USER.physical_inventory table? > >> > > >> > > >> > > >> > Declare @datestring varchar(40) > >> > set @datestring = t1.notes > >> > select substring(@datestring, charindex('exp:', @datestring)+4, > >> > len(@datestring) - (charindex('exp:', > >> > @datestring)) - 4) > >> > from COPACKER_USER.physical_inventory t1 > >> > > >> > > >> > I get the following error: > >> > > >> > Server: Msg 107, Level 16, State 2, Line 2 > >> > The column prefix 't1' does not match with a table name or alias name > >> > used > >> > in the query > > > Not a problem, just a good learning for us all. We assumed and didn't ask.
-- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "john d" <jo***@discussions.microsoft.com> wrote in message news:4CF6B93B-8E5A-41EA-B385-5913FB3D56CC@microsoft.com... > Thank you for your help - I did not know at the time I submitted the > question > that the Notes field was a text/ntext datatype - next time I will ensure I > provide that type of information. I do thank everyone that worked on > this. > > "Arnie Rowland" wrote: > >> Apparently, the Notes field is a text/ntext datatype. >> >> It would have been good to know that earlier. It would have saved several >> folks some time trying to find a solution for you. >> >> Most of the suggestions offered so far will not work on a text/ntext >> datatype. >> >> A couple of choices come to mind. >> >> 1. ALTER the table to change the Notes field to varchar(), select an >> appropriate lenght. >> 2. In the UPDATE query below, cast each instance of the Notes column as >> varchar() >> 3. If you cannot change the Notes datatype in the table, and if the >> dataset >> is not very 'large', load it into a #Temp table, changing the field >> datatype >> in the #Temp table and update with a join. >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "john d" <jo***@discussions.microsoft.com> wrote in message >> news:23DA9517-D4AD-439B-A1DE-7FAD35E7FEF7@microsoft.com... >> > Arnie, >> > getting real close - this is the error that came back >> > >> > "Server: Msg 8116, Level 16, State 2, Line 1 >> > Argument data type text is invalid for argument 1 of len function." >> > >> > "Arnie Rowland" wrote: >> > >> >> How about trying something like: >> >> >> >> UPDATE COPACKER_USER.physical_inventory >> >> SET Exp_Date = substring(Notes, patindex('%exp: %', Notes) + 5, >> >> (len(Notes) - (patindex('%exp: %', Notes) + 5))) >> >> WHERE Notes LIKE '%exp: %' >> >> >> >> -- >> >> Arnie Rowland, Ph.D. >> >> Westwood Consulting, Inc >> >> >> >> Most good judgment comes from experience. >> >> Most experience comes from bad judgment. >> >> - Anonymous >> >> >> >> >> >> "john d" <jo***@discussions.microsoft.com> wrote in message >> >> news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... >> >> > With this example how can I set the @datestring = whatever is in the >> >> > Notes >> >> > field of the COPACKER_USER.physical_inventory table? >> >> > >> >> > >> >> > >> >> > Declare @datestring varchar(40) >> >> > set @datestring = t1.notes >> >> > select substring(@datestring, charindex('exp:', @datestring)+4, >> >> > len(@datestring) - (charindex('exp:', >> >> > @datestring)) - 4) >> >> > from COPACKER_USER.physical_inventory t1 >> >> > >> >> > >> >> > I get the following error: >> >> > >> >> > Server: Msg 107, Level 16, State 2, Line 2 >> >> > The column prefix 't1' does not match with a table name or alias >> >> > name >> >> > used >> >> > in the query >> >> >> Declare @datestring varchar(40)
set @datestring = (select substring(@datestring, charindex('exp:', @datestring)+4, len(@datestring) - (charindex('exp:', @datestring)) - 4) from COPACKER_USER.physical_inventory t1) Show quote "john d" <jo***@discussions.microsoft.com> wrote in message news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... > With this example how can I set the @datestring = whatever is in the Notes > field of the COPACKER_USER.physical_inventory table? > > > > Declare @datestring varchar(40) > set @datestring = t1.notes > select substring(@datestring, charindex('exp:', @datestring)+4, > len(@datestring) - (charindex('exp:', > @datestring)) - 4) > from COPACKER_USER.physical_inventory t1 > > > I get the following error: > > Server: Msg 107, Level 16, State 2, Line 2 > The column prefix 't1' does not match with a table name or alias name used > in the query. > This time it returns a different error:
Server: Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (315128 row(s) affected) Show quote "rcul***@ranger-systems.com" wrote: > Declare @datestring varchar(40) > > set @datestring = (select substring(@datestring, charindex('exp:', > @datestring)+4, > len(@datestring) - (charindex('exp:', > @datestring)) - 4) > from COPACKER_USER.physical_inventory t1) > > > > "john d" <jo***@discussions.microsoft.com> wrote in message > news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... > > With this example how can I set the @datestring = whatever is in the Notes > > field of the COPACKER_USER.physical_inventory table? > > > > > > > > Declare @datestring varchar(40) > > set @datestring = t1.notes > > select substring(@datestring, charindex('exp:', @datestring)+4, > > len(@datestring) - (charindex('exp:', > > @datestring)) - 4) > > from COPACKER_USER.physical_inventory t1 > > > > > > I get the following error: > > > > Server: Msg 107, Level 16, State 2, Line 2 > > The column prefix 't1' does not match with a table name or alias name used > > in the query. > > > > > The approach I suggested assumes that there would only be one match to the
query. If there are (or might be) more than one, you'd have to decide how you would differentiate them, say the first record entered or the last. Then you would modify the query to read: set @datestring = (select top 1 substring(@datestring, charindex('exp:', @datestring)+4, len(@datestring) - (charindex('exp:', @datestring)) - 4) from COPACKER_USER.physical_inventory t1 Order by ?whatever?) Show quote "john d" <jo***@discussions.microsoft.com> wrote in message news:4A0ED6F4-DA03-4197-97C0-387C2343038E@microsoft.com... > This time it returns a different error: > > Server: Msg 512, Level 16, State 1, Line 2 > Subquery returned more than 1 value. This is not permitted when the > subquery > follows =, !=, <, <= , >, >= or when the subquery is used as an > expression. > > (315128 row(s) affected) > > "rcul***@ranger-systems.com" wrote: > >> Declare @datestring varchar(40) >> >> set @datestring = (select substring(@datestring, charindex('exp:', >> @datestring)+4, >> len(@datestring) - (charindex('exp:', >> @datestring)) - 4) >> from COPACKER_USER.physical_inventory t1) >> >> >> >> "john d" <jo***@discussions.microsoft.com> wrote in message >> news:68FB2258-1FD5-4F28-91A5-4315E6B57A5C@microsoft.com... >> > With this example how can I set the @datestring = whatever is in the >> > Notes >> > field of the COPACKER_USER.physical_inventory table? >> > >> > >> > >> > Declare @datestring varchar(40) >> > set @datestring = t1.notes >> > select substring(@datestring, charindex('exp:', @datestring)+4, >> > len(@datestring) - (charindex('exp:', >> > @datestring)) - 4) >> > from COPACKER_USER.physical_inventory t1 >> > >> > >> > I get the following error: >> > >> > Server: Msg 107, Level 16, State 2, Line 2 >> > The column prefix 't1' does not match with a table name or alias name >> > used >> > in the query. >> > >> >> >>  |
|||||||||||||||||||||||
Other interesting topics