SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to select data from text strings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

demifelix
Starting Member

22 Posts

Posted - 11/10/2007 :  21:03:30  Show Profile  Reply with Quote
Hi everyone,

You have helped me resolved my previous problem with the LIKE statement, and now I'm running into this TEXT STRING problem that I desperately need your help and guidance again.

The following is the set of various descriptions in a PRODUCT_DESC field. I need to be able to calculate the Squared Meter of these products. As you can see, I need to be able to extract the part in the middle (like 2x60YD, etc.) for each record and perform some sort of calculation and conversion. The problem is that I can't find a way to do this effectively. Can someone please help me? Thanks.

PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
CH PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/10/2007 :  21:31:45  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
How many different variations of the string are there?

IF there are a lot of combinations with no real consistency (you need something that appears in EVERY string (like IPG for example always occurrs or the letters MIL are always 4 characters past the measurement..) it will be tricky.

Given the layout of that string, I would actually paste into excel, convert text to columns using space as the delimiter and then your measurement will be by itself its own column.

If a solution were proposed for that sample, it may not work on every row (what if IPG Premium or "Masking Tape" do not occur in every row?)
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 11/10/2007 :  22:54:07  Show Profile  Reply with Quote
You can use charindex function to get position of specific character then take substring from there.
Go to Top of Page

demifelix
Starting Member

22 Posts

Posted - 11/11/2007 :  09:47:20  Show Profile  Reply with Quote
Dataguru,

The problem is that I need to build this whole calculation in SQL, so I can't have Excel in here.

1 consistency that I see in these records is the numbers before and after the 'X' character somewhere in the string. It has different variations, but it all boils down to something like '2X60YD' or '24MMX55' or something similar to these. So, if I can find a way to grab what's in front of the X (like 2 and 24MM in the examples above) and what's after the X (like 60YD and 55 above) then I can take it from there.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/11/2007 :  09:57:50  Show Profile  Reply with Quote
SELECT	data, [before], [after], SUBSTRING(data, [before], [after] - [before])
FROM
(
	SELECT	data, 
		[before] = LEN(data) - CHARINDEX(' ', REVERSE(data), CHARINDEX('X', REVERSE(data))) + 2,
		[after]  = CHARINDEX(' ', data, CHARINDEX('X', data))
	FROM
	(
		SELECT	'PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'	AS data UNION ALL
		SELECT	'CH PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'
	) d
) d



KH
Time is always against us

Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/11/2007 :  10:08:48  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
quote:
Originally posted by demifelix

Dataguru,

The problem is that I need to build this whole calculation in SQL, so I can't have Excel in here.

1 consistency that I see in these records is the numbers before and after the 'X' character somewhere in the string. It has different variations, but it all boils down to something like '2X60YD' or '24MMX55' or something similar to these. So, if I can find a way to grab what's in front of the X (like 2 and 24MM in the examples above) and what's after the X (like 60YD and 55 above) then I can take it from there.



It is that consistency that is needed to use the Charindex as khtan posted and rmaio mentioned. If there weren't something consistent in each field it would be tricky.

I would have looked at parse it based on an sproc along the lines of this idea, to parse it by spaces and extract the column from there (had there not been something consistent in each field to use charindex with)

http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx
Go to Top of Page

demifelix
Starting Member

22 Posts

Posted - 11/11/2007 :  16:44:56  Show Profile  Reply with Quote
Thanks khtan. Is there anyway I can just pass the field name instead of selecting the individual text strings like this? I have over 10,000 records and this will be impossible.

quote:
Originally posted by khtan

SELECT	data, [before], [after], SUBSTRING(data, [before], [after] - [before])
FROM
(
	SELECT	data, 
		[before] = LEN(data) - CHARINDEX(' ', REVERSE(data), CHARINDEX('X', REVERSE(data))) + 2,
		[after]  = CHARINDEX(' ', data, CHARINDEX('X', data))
	FROM
	(
		SELECT	'PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'	AS data UNION ALL
		SELECT	'CH PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM'	UNION ALL
		SELECT	'PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM'
	) d
) d



KH
Time is always against us



Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/11/2007 :  16:55:47  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
He wasn't suggesting that you specify each string. He was showing you how to use charindex and substring to solve your problem.

Change data to your field name and replace all those strings in the FROM statement with your table name.


SELECT	data, [before], [after], SUBSTRING(data, [before], [after] - [before])
FROM
(
	SELECT	data, 
		[before] = LEN(data) - CHARINDEX(' ', REVERSE(data), CHARINDEX('X', REVERSE(data))) + 2,
		[after]  = CHARINDEX(' ', data, CHARINDEX('X', data))
	FROM  (Select [YourColumnHere] FROM [YourTableNameHere] ) d
) d





Poor planning on your part does not constitute an emergency on my part.


Edited by - dataguru1971 on 11/11/2007 16:56:34
Go to Top of Page

arorarahul.0688
Posting Yak Master

India
125 Posts

Posted - 11/12/2007 :  03:39:08  Show Profile  Reply with Quote
select substring(column_name,patindex('%tape%',column_name)+5,patindex('%MIL%',column_NAME)-patindex('%tape%',column_name)-5) from table_name

this will fetch you
2X60YD 7.3
2X60YD 6.4
24MMX55 7.3
24MMX55 7.3
2X60YD 7.3
2X60YD 6.4
24MMX55 7.3
2X60YD 7.3
this data and now you can easily use these statistics to manipulate to get desired result

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE
Go to Top of Page

demifelix
Starting Member

22 Posts

Posted - 11/12/2007 :  10:38:21  Show Profile  Reply with Quote
Thanks everyone for your help. I took your ideas and have it worked.
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  10:53:42  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
quote:
Originally posted by arorarahul.0688

select substring(column_name,patindex('%tape%',column_name)+5,patindex('%MIL%',column_NAME)-patindex('%tape%',column_name)-5) from table_name

this will fetch you
2X60YD 7.3
2X60YD 6.4
24MMX55 7.3
24MMX55 7.3
2X60YD 7.3
2X60YD 6.4
24MMX55 7.3
2X60YD 7.3
this data and now you can easily use these statistics to manipulate to get desired result

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE




That won't work for things without tape. If you followed the discussion at all, you would have seen that the solution posted by khtan covered all scenarios. Your solution only covers the exact subset of data provided as a sample, and would not solve the OP's actual problem for records with TAPE or MIL in the string in those exact positions.




Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000