Help needed in separating values

Question

Post reply

Help needed in separating values

KGJ-Dev

SSCertifiable

Points: 5780
More actions
July 28, 2015 at 3:58 pm

#299263

Hi,
Below are the same data
with Sample(Size) as (
select '16.3 Oz.' union all
'1' as union all
'2 Tablespoons' union all
'46. Oz. Each' )
i want to separate number and alphabets. But i wanted to keep the dot to have decimal values. Expected output
select '16.3' as val1 'Oz' as val2 union all
'1' val1 as union all
'2' as val1 'Tablespoons' as val2 union all
'46' as val1 'Oz Each' as val2
please note that i need to remove the extra dots at the end of the the val1 and no dots in val2
I rid some of the functions like dbo.fn_StripCharacters and dbo.fn_GetAlphabetsOnly ffound in the internet. evey with my own logic. i couldn;t remove the dot wihc appear at the end of val1
Any sample please. how to achieve in better way.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Can you guarantee the format of that data will be consistent?

For example, will you ever see "1.Oz." (no spaces) or "1.. Oz." or " 1 Oz"?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

WayneS SSC Guru Points: 95460 More actions · Answer 2

KGJ-Dev (7/28/2015)
Hi,
Below are the same data
with Sample(Size) as (
select '16.3 Oz.' union all
'1' as union all
'2 Tablespoons' union all
'46. Oz. Each' )
i want to separate number and alphabets. But i wanted to keep the dot to have decimal values. Expected output
select '16.3' as val1 'Oz' as val2 union all
'1' val1 as union all
'2' as val1 'Tablespoons' as val2 union all
'46' as val1 'Oz Each' as val2
please note that i need to remove the extra dots at the end of the the val1 and no dots in val2
I rid some of the functions like dbo.fn_StripCharacters and dbo.fn_GetAlphabetsOnly ffound in the internet. evey with my own logic. i couldn;t remove the dot wihc appear at the end of val1
Any sample please. how to achieve in better way.

Does this work for you? (comments on how it works are in the code)

WITH Sample(Size) AS

(

SELECT *

FROM (VALUES('16.3 Oz.'), ('1'), ('2 Tablespoons'), ('46. Oz. Each') ) dt(col)

)

SELECT --*,

CASE

-- if the position is a space, and the preceding character is a period, remove the period

WHEN SUBSTRING(Sample.Size, ca1.Pos, 1) = ' ' AND

SUBSTRING(Sample.Size, ca1.Pos-1, 1) = '.'

THEN LEFT(Sample.Size, ca1.Pos-2)

-- if the string only has numerics, return the entire string

WHEN ca1.Pos = 0 THEN Sample.Size

-- otherwise, get the left characters up to (but not including) the first non-numeric & non-period character

ELSE LEFT(Sample.Size, ca1.Pos-1)

END AS Val1,

-- get the rest of the string.

-- replace any periods with an empty string

-- perform a LTRIM on the result

LTRIM(REPLACE(

CASE

WHEN ca1.Pos = 0 THEN NULL

ELSE SUBSTRING(Sample.Size, ca1.Pos, LEN(Sample.Size))

END, '.', '')) AS Val2

FROM Sample

-- find the first non-numeric and non-period in the string

CROSS APPLY (SELECT PATINDEX('%[^0-9.]%', Sample.Size)) ca1(Pos);

(Note that I made the cte for the sample data actually work).

My results:

Val1 Val2

------------- -------------

16.3 Oz

1 NULL

2 Tablespoons

46 Oz Each

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 3

Hi mister,

nope, the data is not consistent.

Hi WayneS,

thank you so much for working query. I am curious to learn about cross apply used here. I will play with that. Appreciated your time on this post.

WayneS SSC Guru Points: 95460 More actions · Answer 4

KGJ-Dev (7/28/2015)
Hi mister,
nope, the data is not consistent.
Hi WayneS,
thank you so much for working query. I am curious to learn about cross apply used here. I will play with that. Appreciated your time on this post.

For learning about CROSS APPLY, see the "Using APPLY Part 1" and "Part 2" links in my signature below.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

KGJ-Dev

SSCertifiable

Points: 5780

July 29, 2015 at 5:13 am

#1815661

Thank you Wayne.