Format Data - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Format Data

Author

Topic

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-13 : 16:25:29

Hi, I using the code column as a primary key to join to another table but some of the codes are in the following incorrect format

- 574149/17 (WHOLE BOX)
- 584071/1+584072/1
- 582902/33 (2 FILES)
- 574149/1745(WHOLE BOX)

The above should be corrected as below

- 574149/17
- 584071/1
- 582902/33
- 574149/1745

create table tbltable1
(
ID int,
Firstname varchar (50),
Lastname varchar (50),
Code varchar (50),
);
Insert into tbltable1
Values

(100, 'john', 'ribbery','574149/17 (WHOLE BOX)'),
(101,'jonathan' ,'Rob', '584071/1+584072/1'),
(102,'simon' ,'Rob', '584071/5'), -- example, correct format
(103, 'Rob', 'berry', '582902/33 (2 FILES)'),
(104, 'Michael', 'Dawson', '574149/1745(WHOLE BOX)')

select
*
from
table1 inner join
tbltable2 on tbltable1.code = tbltable2.code

Many Thanks

Marcus

I learn something new everyday.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 08:53:35

you should change tbltable1 so that it has an extra column for the text after the '/nn' eg.



ID int,
Firstname varchar (50),
Lastname varchar (50),
Code varchar (50),
Extra varchar(50)
);
Insert into tbltable1
Values

(100, 'john', 'ribbery','574149/17', '(WHOLE BOX)'),
(101,'jonathan' ,'Rob', '584071/1', '+584072/1'),
(102,'simon' ,'Rob', '584071/5', null), -- example, correct format
(103, 'Rob', 'berry', '582902/33', '(2 FILES)'),
(104, 'Michael', 'Dawson', '574149/1745', '(WHOLE BOX)')

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-14 : 16:10:20

To narrow my question down. I have found a different workaround. How can i trim non numeric values after '/' so

- 574149/17 (WHOLE BOX)
- 584071/1+584072/1
- 582902/33 (2 FILES)

so i would like the following to exclude the first '/' in string and then trim anyvalues that strart with none numeric

-- Substring(Code, Patindex('%[^0-9]%',Code),10) as StrippedValues

Example

http://blog.sqlauthority.com/2012/10/14/sql-server-find-first-non-numeric-character-from-string/

Thanks,

Marcus

I learn something new everyday.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-14 : 16:58:54

[code]
select
*,
LEFT(code, CHARINDEX('/', code) + PATINDEX('%[^0-9]%', SUBSTRING(code, CHARINDEX('/', code) + 1, 8000) + '.') - 1)
from
tbltable1
[/code]

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-14 : 17:25:10

Thanks Scott, Perfect !!!

Marcus

I learn something new everyday.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-14 : 17:38:16

You're very welcome!

To avoid repeating this logic, and to make it easy to change/correct later, you could consider adding it as a computed column to the original table:

ALTER TABLE tbltable1
ADD code_numeric AS LEFT(code, CHARINDEX('/', code) + PATINDEX('%[^0-9]%', SUBSTRING(code, CHARINDEX('/', code) + 1, 8000) + '.') - 1)

Then you can directly SELECT it, do WHERE on it, ORDER BY it, etc..

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-15 : 06:19:07

Assuming the data has been modified using Scotts solution, how can i extract the first numeric values before the stroke and numeric values after the stroke

(.i.e)
Extracting first numeric values before '/'. Works fine.
LEFT(code, PATINDEX('%[^0-9]%', code) - 1)- output 584071
584071/123 Output 584071

Extracting last numeric values after '/'. Almost works but my results include '/'
substring(code, Patindex('%[!-/]%%',code),10)
584071/123 Output /123 -
corect output should be 123

Thanks for the help !

Marcus

I learn something new everyday.

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-15 : 08:14:03

Sorted

Substring(replace (code,'/',''), Patindex('%[^0-9]%',code),10).

Marcus

I learn something new everyday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources