June 15, 2010 at 11:53 pm
We have some records with a column called 'Description' which contain a data element that I would like to extract with in intention of adding this data to another table and column (Dimensions). I have used temporary tables to extract a substring of the data and to clean it up a step at a time, but have reached the limits of my abilities and would be grateful for some assistance with my syntax.
The original data is like this: "Photograph of two funnel passenger ship r.M.M.V. "Asturias". Bow/ starboard side view. Printed in black ink along lower border of card "R.M.M.V. "Asturias". Typed on white paper strip below card "R.M.M.V. "Asturias". Dimensions: 74 x 138mm. [Page 1. Items [001] - [010] ]."
I would like to pull out the number immediately following “Dimensions:” and have used the following scripts to begin this process.
--selectObjectID,
--ObjectNumber,
--Description,
--substring (description, charindex ('Dimension', description),15) as NewDimension
--
--into#TempDimension
--
--fromObjects
--
--whereObjectNumber like 'ANMS0599%'
-------------------------------------------------------
--selectObjectNumber,
--NewDimension,
--substring (NewDimension,12,4) as Height
--
--from#TempDimension
--
-----------------------------------------------------
--selectObjectNumber,
--NewDimension,
--substring (NewDimension,12,4) as Height
--
--into#NewHeight
--
--from#TempDimension
-------------------------------------------------------
selectObjectNumber,
NewDimension,
Height,
left(ltrim(Height),3) as Height_1,
SUBSTRING(Height, CHARINDEX(':', Height) + 1, LEN(Height)) AS Height_2
from #NewHeight
The column #NewHeight.Height_1 is close to meeting my needs, but I am left with some data with ‘:’. Likewise NewHieght_2 gives me partial success but I am left with the 'x' in my data. Please see sample data below.
ObjectNumberNewDimensionHeightHeight_1Height_2
ANMS0599[107] Dimensions:108 108108108
ANMS0599[151] Dimensions:84 x84 x8484 x
ANMS0599[152] Dimensions:85 x85 x8585 x
ANMS0599[153] Dimensions:88 x88 x8888 x
ANMS0599[154] Dimensions:88 x88 x8888 x
ANMS0599[155] Dimensions:90 x90 x9090 x
ANMS0599[157] Dimensions:69 x69 x6969 x
ANMS0599[158] Dimensions :88 :88 :8888
ANMS0599[159] Dimensions :52 :52 :5252
ANMS0599[160] Dimensions :55 :55 :5555
ANMS0599[173] Dimensions :85 :85 :8585
ANMS0599[174] Dimensions :84 :84 :8484
ANMS0599[175] Dimensions :86 :86 :8686
ANMS0599[176] Dimensions :86 :86 :8686
ANMS0599[201] Dimensions: 84 848484
ANMS0599[202] Dimensions: 83 838383
ANMS0599[203] Dimensions: 87 878787
I have attached an excel sample of the original data and my final temporary table data.
I would like to be able to remove the ‘X’ and the ‘:’ in one go if at all possible but do not know how. If this is not possible, would like to know how to pare my data back so that I am left with just the numbers in a single column for updating later.
Grateful for any assistance you can give me.
Regards, Sally
June 16, 2010 at 2:05 am
Fletcher, i know how to go about it ; but, but but, i would like to know HOW your original data looks like and how you want your data to be.. In the excel you had given , i am unable to find the full "Dimension" of any row.. Please provide me with good-clear original data and a picture of your final data, i will (and many other guys here) provide with high-performing code!
June 16, 2010 at 5:52 am
I'd also suggest reading the first link in my signature for a better way of posting your data... a way that will get people to actually help you out, since they will be able to just cut-and-paste your data, and start working on a solution for you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 16, 2010 at 10:18 pm
Dear ColdCoffee
Thank you very much for responding to my request for help. I have tried to follow Wayne's advice (below) re posting my data so I hope I have done this correctly. I have just copied the SQL here. Is this what you need? Please let me know as I am keen to learn and would like to use this forum in the future.
--IF OBJECT_ID('TempDB..#TempObjects','U') IS NOT NULL
--DROP TABLE #TempObjects
--===== Create test table
--create table #TempObjects
--(
--ObjectIDint Primary key,
--ObjectNumberchar (42) NOT NULL,
--Descriptiontext
--)
-------------------------------------------------------
--SELECT 'SELECT '
--+ QUOTENAME(ObjectID,'''')+','
--+ QUOTENAME(ObjectNumber,'''')+','
--+ CAST(Description AS VARCHAR (4000))+',' --Please note - not sure what I am doing here as I was not able to pick up all the data - only a truncated set of data from this TEXT field. I subsequently copied and pasted the correct data from my db!
--+ ' UNION ALL'
--FROM Objects
--
--where ObjectNumber like 'ANMS0599%'
------------------------------------------------------------
--===== Insert the test data into the test table
--this is only a small subset of data, but contains a sample of the variety of data in the Description field
SET IDENTITY_INSERT #TempObjects ON
Insert into #TempObjects (ObjectID, ObjectNumber, Description)
SELECT '82824','ANMS0599[001] ','Postcard. Photograph of three funnelled passenger ship ss "Ile de France". Bow/ starboard view. In harbour with tugs at stern and bow. Owned by Cie Generale Transatlantique, Paris. Printed in black ink along lower border of card "S.S. Ile de France" (Cie Gle. Translantique) - fitted by". Typed on white paper strip below card "S.S."Ile de France". Dimensions: 75 x 138mm. [Page 1. Items [001] - [010] ].' UNION ALL
SELECT '82825','ANMS0599[002] ','Postcard. Photograph of passenger/cargo ship ss "Koningin emma". Bow/ starboard view. At sea, port bow anchor lowered. All lifeboats removed. Apparently sinking. Owned by stoomvaart mij zeeland[?]. Printed in black ink along lower border of card "Sinking of s.S. "Koningin emma" - Fitted by". Typed on white paper strip below card "S.S."Konigin[sic] emma". Dimensions: 72 x 138mm. [Page 1. Items [001] - -[010] ].' UNION ALL
SELECT '82826','ANMS0599[003] ','Postcard. Photograph of passenger/cargo ship mv "Accra". Port side view. At anchor at sea. Printed in black ink along lower border of card "M.V. "Accra" (Elder dempster & co., Ltd.)" - Fitted by". Typed on white paper strip below card "M.V "Accra". Dimensions: 74 x 138mm. [Page 1. Items [001] - [010] ].' UNION ALL
SELECT '82884','ANMS0599[061] ','Photograph. S.S. "Century". Cargo ship. Single funnel. Two masts. Starboard side view. Vessel berthed at wharf on port side. Owned by ?howard smith limited, melbourne. Typed on paper strip below photograph "S.S."Century" ". Dimensions:125 x 175mm. [Page 15. Items [058]- [061] ].' UNION ALL
SELECT '82885','ANMS0599[062] ','Photograph. S.S. "Quorna". Cargo ship. Single funnel. Two masts. Bow/port side view. Vessel apparently leaving port, under way, passing light on starboard side. Land in distance off stern. Owned by [?] adelaide ss company, adelaide. [See note under "Remarks"]. Typed on paper strip below photograph "S.S."Quorna" ". Dimensions:116 x 158mm.[Page 16. Items [062] - [067] ]' UNION ALL
SELECT '82886','ANMS0599[063] ','Photograph. R.M.S. "Macedonia". Passenger ship. Two funnels. Two masts. Port side view. Under way at sea. Vessel owned by the peninsular & oriental steam navigation company. Built 1904. Under admiralt control 1914-1917. Sold 1931.Typed on white paper strip below photograph "R.M.S. "Macedonia". Dimensions: 90 x 140mm. [Page 16. Items [062] - [067] ].' UNION ALL
SELECT '82887','ANMS0599[064] ','Photograph. S.S. "Calera". Cargo/passenger ship. Two funnels. Two masts. Stern/port side view. Vessel berthed at wharf on starboard side. Typed on paper strip below photograph "S.S. "Calera" ". Dimensions:113 x 158mm. Small area of corners of photograph cut off. [Page 16. Items [062] - [067] ].' UNION ALL
SELECT '82888','ANMS0599[065] ','Photograph. S.S. "Canberra". Passenger/cargo ship. Single funnel. Two masts. Starboard side view. Vessel under way at sea. Vessel owned by [?] howard smith limited, melbourne. Typed on paper strip below photograph "S.S. "Canberra" ". Dimensions:105 x 157mm. [Page 16. Items [062] - [067] ].' UNION ALL
SELECT '82889','ANMS0599[066] ','Drawing. Photograph. R.M.S. "Cuzco". Passenger/cargo ship. Steam/sail. Single funnel. Three masts, fore and aft rigged. Sails furled. Bow/port side view. Clipper bow. Vessel under way at sea. Rocky point with light house off stern on port side. Steam ship in distance off stern on starboard side. Handwritten inscription in black letter l.L.C. "Cuzco rounding cape st. Vincent". Vessel owned by pacific steam navigation company, liverpool. Typed on paper strip below photograph "R.M.S. "Cuzco" ". Dimensions:105 x 157mm. [Page 16. Items [062] - [067] ].' UNION ALL
SELECT '82916','ANMS0599[093] ','Postcard. Photograph. S.S. "Kowarra". Cargo ship. Single funnel. Two masts. View of bow/starboard side of vessel. Berthed at wharf on port side. Vessel owned by howard smith ltd., From 1916 when requisitioned by admiralty until 1919. Subsequently sunk by japanese submarine off sandy cape, queensland 24/4/1943. Typed on white paper strip below photograph "S.S. "Koowarra [sic. "Kowarra"]" ". Dimensions:87 x 137mm. [Page 20. Items [085] - [093] ].' UNION ALL
SELECT '82917','ANMS0599[094] ','Photograph. S.S. "Marrawah"[?]. Cargo ship. Single funnel. Two masts. View of port side of vessel. Berthed at wharf on starboard side. Another vessel with similar funnel is berthed off bow at c.L.. Typed on white paper strip below photograph "S.S. Marrawah". Dimensions:88 x 146mm. [Page 21. Items [094] - [101] ].' UNION ALL
SELECT '82919','ANMS0599[096] ','Photograph. S.S. "Ellaroo". Cargo ship. Single funnel.Two masts. Starboard view of vessel. Berthed at wharf on port side. Owned by melbourne steam ship company, melbourne. Typed on white paper strip below photograph "S.S. Elaroo [sic "Ellaroo"]". Dimensions:90 x 147 mm. [Page 21. Items [094] - [101] ].' UNION ALL
SELECT '82424','ANMS0599[158] ','Photograph. S.S"Aberdeen". Cargo/?passenger ship. Steam/sail. Single funnel. Three masts, aft mast fore and aft, others square rigged. Sails furled. Port side view. Under way in harbour. . Typed on white paper strip below photograph "S.S."Aberdeen". Dimensions :88 x 94mm. [Page 28. Items [156] - [166] ].' UNION ALL
SELECT '82425','ANMS0599[159] ','Photograph. S.S"Armagh". Cargo ship. Single funnel.?Three masts. Starboard side view. Berthed at wharf on port side. Vessel owned by [?] federal steam navigation company limited, london. Typed on white paper strip below photograph "S.S."Armagh". Dimensions :52 x 84mm. Faded, light brown staining. Emulsion damage. [Page 28. Items [156] - [166] ].' UNION ALL
SELECT '82919','ANMS0599[160] ','Photograph. S.S"Anchises". Passenger/cargo ship. Single funnel. Two masts. Bow/ starboard side view. Near wharf on port side. Tug at stern, and line from bow to [?]tug [not seen] at c.R.. Typed on white paper strip below photograph "S.S."Anchisis [sic "[?]Anchises"]". Dimensions :55 x 84mm. Severe reddish staining with darkening of image and loss of detail. [Page 28. Items [156] - [166] ].'
SET IDENTITY_INSERT #TempObjects ON
June 16, 2010 at 10:22 pm
Dear Wayne
Thank very much for your advice which I have tried to use to respond to the above post. I hope that I have done this correctly as I find this forum very helpful and am keen to learn how to use this resource properly.
kind regards, Sally
June 16, 2010 at 11:34 pm
Fletcher, thanks for posting the data in readily consumable format; Here i am giving u a piece of code that will retreive the dimension values from the "Description" column. Please tell us if thats what you were looking after.
; WITH Converted_Data AS
(
SELECT ObjectID, ObjectNumber, CAST ([Description] AS VARCHAR(MAX)) [Description] ,
CHARINDEX( 'Dimensions' , (CAST ([Description] AS VARCHAR(MAX))) ) Dim_Pos
FROM #TempObjects
),
New_Dimensions AS
(
SELECT ObjectID, ObjectNumber ,
SUBSTRING ( [Description] , Dim_Pos , ((CHARINDEX( 'mm' ,[Description],Dim_Pos) + 2) - Dim_Pos)) Descriptive_Dimension
FROM Converted_Data
),
Positions AS
(
SELECT *,
CHARINDEX(':',Descriptive_Dimension ) Colon_Pos,
CHARINDEX('x',Descriptive_Dimension ) x_Pos,
CHARINDEX('mm',Descriptive_Dimension ) mm_Pos
FROM New_Dimensions
)
SELECT ObjectID, ObjectNumber , Descriptive_Dimension,
RTRIM( LTRIM ( SUBSTRING(Descriptive_Dimension, (Colon_Pos + 1) , (x_Pos - Colon_Pos -1) ) )) Height,
RTRIM(LTRIM(SUBSTRING(Descriptive_Dimension,(x_Pos + 1), (mm_Pos - x_Pos - 1 ) )) ) Breadth
FROM Positions
Side Note: Please see my above T-SQL code, it looks similar to the one u type in ur Query Editor window right; to post your code so that it looks like the above, please utilize the IFCode and ;
Hope this helps you! Tell us if we have to do more with the sample data!
June 17, 2010 at 10:24 pm
Dear ColdCoffee
Thank you very much for going to so much effort to help me solve my substring problem. Unfortunately I am working in SQL2000 so I think the WITH statement doesn't work in this version (should have told you what version we have). Anyway, I thought that perhaps I could adapt your code and make a bunch of temporary tables, (or perhaps a view) from which I could work my eventual update script.
The temp tables seem to work well except when I create the final table (#SF_FinalDimensions) at which point I get a message 'Invalid length parameter passed to the substring function'. I think this is due to some data not containing the expected CHARINDEX or have a null value in the Description field. I thought that perhaps I should use NULLIF but I don't know how to write this syntax, or really where it should go. I have also tried removing these non confirming records from my original selection into the first table (#ST_TempObjects) but this doesn't effect the final result.
If I limit the selection of records that are inserted into this table to the first few records (#SF_FinalDimensions) the script works beautifully thank you and I get the dimension values I was hoping for. However, even then, I don't get all the records that I would expect to see - always one less record than expected.
I have posted my code hopefully in the correct manner. Do you need me to recreate the data according to all these tables I have made?
kind regards and thanks again
Sally
--Create temp table #SF_TempObjects
use TMS2
go
--drop table #SF_TempObjects
create TABLE #SF_TempObjects
(
ObjectID int,
ObjectNumberchar (42),
Description text
)
INSERT INTO #SF_TempObjects (ObjectID, ObjectNumber, Description)
SELECT ObjectID, ObjectNumber, Description
FROM Objects
WHERE ObjectNumber like 'ANMS0599%'
--and Description like '%dimensions:%' --Unsuccessful -Added to avoid "Invalid length parameter passed to the substring function" when try ing to create/select from temp table #SF_FinalDimensions
--and Description is not null --Unsuccessful -Added to avoid "Invalid length parameter passed to the substring function" when try ing to create/select from temp table #SF_FinalDimensions
-----------------------------------------------------
Select * from #SF_TempObjects
--===========================================
--Create temp table #SF_Converted_Data
SELECT ObjectID, ObjectNumber, CAST ([Description] AS VARCHAR(2000)) [Description] , --MAX causes syntax error
CHARINDEX( 'Dimensions' , (CAST ([Description] AS VARCHAR(2000))) ) Dim_Pos -- MAX causes syntax error
--drop table #SF_Converted_Data
into#SF_Converted_Data
from #SF_TempObjects
----------------------------------------------------------
select * from #SF_Converted_Data
--============================================
--create temp table #SF_New_Dimensions
selectObjectID,
ObjectNumber ,
SUBSTRING ( [Description] , Dim_Pos , ((CHARINDEX( 'mm' ,[Description],Dim_Pos) + 2) - Dim_Pos)) Descriptive_Dimension
--drop table #SF_New_Dimensions
into#SF_New_Dimensions
FROM#SF_Converted_Data
--------------------------------------------------------
select * from #SF_New_Dimensions
--============================================
--New temp table #SF_Positions
SELECT *,
CHARINDEX(':',Descriptive_Dimension ) Colon_Pos,
CHARINDEX('x',Descriptive_Dimension ) x_Pos,
CHARINDEX('mm',Descriptive_Dimension ) mm_Pos
--NULLIF (CHARINDEX(':',Descriptive_Dimension )) Colon_Pos,
-- NULLIF (CHARINDEX('x',Descriptive_Dimension ) ) x_Pos,
-- NULLIF (CHARINDEX('mm',Descriptive_Dimension )) mm_Pos
--drop table #SF_Positions
into #SF_Positions
FROM #SF_New_Dimensions
-------------------------------------------------------
select * from #SF_Positions
--===========================================
--New temp table #SF_FinalDimensions
--Get error message 'Invalid length parameter passed to the substring function' when creating temp table #SF_FinalDimensions
--Works fine if I limit my selection into this table to a record such as ANMM0599[001] or a small group such as below
--Tried to select only those records without null values in Description field when creating original temp table #SF_TempObjects no assistance,
SELECT ObjectID, ObjectNumber , Descriptive_Dimension,
RTRIM( LTRIM ( SUBSTRING(Descriptive_Dimension, (Colon_Pos + 1) , (x_Pos - Colon_Pos -1) ) )) Height,
RTRIM(LTRIM(SUBSTRING(Descriptive_Dimension,(x_Pos + 1), (mm_Pos - x_Pos - 1 ) )) ) Breadth
--drop table #SF_FinalDimensions
into #SF_FinalDimensions
FROM #SF_Positions
where ObjectNumber in ('ANMS0599[001] ','ANMS0599[002]','ANMS0599[003]','ANMM0599[004]') --works if I limit the selection, although I get one less record than expected
--where ObjectID in (select ObjectID from #SF_TempObjects)
--where Descriptive_Dimension is not null DOES NOT WORK
---------------------------------------------------------
select * from #SF_FinalDimensions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply