December 22, 2014 at 10:11 am
Thanks for any help with this... I have a text field with about 17,500 rows I need to edit from for example: '1.90X .90' to '1.90X 0.90' any ideas? The numbers are various but follow the basic format albiet they may vary to say '22.78X .40'
December 22, 2014 at 10:29 am
If it's really as simple as you describe, I think the REPLACE command ought to do it for you.
December 22, 2014 at 10:47 am
If I were doing this one at a time like the below, where could I use the REPLACE that would work with a series like below?:
UPDATE dbo.List with (ROWLOCK) SET Dimensions = '4.1x0.5'
WHERE [ID] = 32275124 and Dimensions = '4.1x.5'
could I use something like this:
UPDATE dbo.List with (ROWLOCK) SET Dimensions = REPLACE(diminsions,'x.','x0.')
December 22, 2014 at 10:59 am
Maybe I'm not seeing a complexity you know about. I would write the update to over the whole table and just run it once. I don't see the need to run it for each case individually. Can you provide some DDL and sample data that takes the various situations you have into account?
December 22, 2014 at 11:17 am
Instead of fighting with this, why don't you do things correctly? You should store each value in its own column.
December 22, 2014 at 11:20 am
If you want to split your string based on the presence of an X, format each part of it and then repopulate your column, you can do this using the DelimitedSplit8K function. If you aren't familiar with the function yet, see the second link in my signature. It's time well spent to learn this function and it'll change the way you look at strings. It will also change your expectations of performance.
Let's say you have your dimensions in the following table with some data.
if OBJECT_ID('tempdb.dbo.#test', 'u') is not null drop table #test;
CREATE TABLE #test (
ID integer not null identity (1, 1),
constraint test_pk primary key (ID),
dimensions varchar(255));
INSERT INTO #test(dimensions)
VALUES('4.1x.5'),
('4.1x0.5'),
('1.90X.90'),
('1.9x0.90');
You can isolate each "side" of each row based on the presence of an X like this:
SELECT t.id, s.ItemNumber, s.Item
FROM #test t
CROSS APPLY DelimitedSplit8K(t.dimensions, 'x') s
ORDER BY t.ID, s.ItemNumber;
From there, you'll want to be able to rebuild your string to contain both sides and include a leading zero on the right side if it doesn't already exist.
WITH cteSplit AS (
SELECT t.id, s.ItemNumber, s.Item
FROM #test t
CROSS APPLY DelimitedSplit8K(t.dimensions, 'x') s
)
SELECT ID,
MAX(CASE WHEN s.ItemNumber = 1 THEN s.Item END) + ' X ' +
MAX(CASE WHEN s.ItemNumber = 2 THEN CASE WHEN SUBSTRING(s.Item, 1, 1) = '0' THEN '' ELSE '0' END + s.Item END)
FROM cteSplit s
GROUP BY id;
From there, you'll need to update your original table with the new string you built:
WITH cteSplit AS (
SELECT t.id, s.ItemNumber, s.Item
FROM #test t
CROSS APPLY DelimitedSplit8K(t.dimensions, 'x') s
),
cteBuilt(ID, Rebuilt) as (
SELECT ID,
MAX(CASE WHEN s.ItemNumber = 1 THEN s.Item END) + ' X ' +
MAX(CASE WHEN s.ItemNumber = 2 THEN CASE WHEN SUBSTRING(s.Item, 1, 1) = '0' THEN '' ELSE '0' END + s.Item END)
FROM cteSplit s
GROUP BY ID
)
UPDATE #test
SET dimensions = b.Rebuilt
FROM cteBuilt b
WHERE #test.id = b.ID;
December 22, 2014 at 11:22 am
Luis Cazares (12/22/2014)
Instead of fighting with this, why don't you do things correctly? You should store each value in its own column.
Luis raises an excellent point here. If you use the first query above, you can isolate each value and store each one in its own numeric column
December 22, 2014 at 1:12 pm
i inherited this database, if I were designing it myself I would have used two fields both being decimals... but this is an ETL so what I got I got
December 22, 2014 at 5:00 pm
Perhaps you can't design it but maybe you can redesign it.
DECLARE @MyDims TABLE
(
Dimensions VARCHAR(100)
,[Length] AS(LEFT(Dimensions, CHARINDEX('X', Dimensions)-1))
,Breadth AS(SUBSTRING(Dimensions, CHARINDEX('X', Dimensions)+1, 255))
);
INSERT INTO @MyDims (Dimensions)
SELECT '1.90X .90'
UNION ALL SELECT '22.78X .40';
SELECT *
FROM @MyDims;
Then you can format length and breadth anyway you like.
Edit: Note that I realize I'm playing fast and loose with any potentially necessary validations, but I think you get the idea.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply