How to select data from text strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to select data from text strings

Author

Topic

demifelix
Starting Member

22 Posts

Posted - 2007-11-10 : 21:03:30

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
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-10 : 21:31:45

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?)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-10 : 22:54:07

You can use charindex function to get position of specific character then take substring from there.

demifelix
Starting Member

22 Posts

Posted - 2007-11-11 : 09:47:20

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-11 : 09:57:50

[code]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[/code]

KH
[spoiler]Time is always against us[/spoiler]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-11 : 10:08:48

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

demifelix
Starting Member

22 Posts

Posted - 2007-11-11 : 16:44:56

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
[spoiler]Time is always against us[/spoiler]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-11 : 16:55:47

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.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-12 : 03:39:08

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

demifelix
Starting Member

22 Posts

Posted - 2007-11-12 : 10:38:21

Thanks everyone for your help. I took your ideas and have it worked.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 10:53:42

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources