CODEWITHSUNDEEP
sql-serverstored-proceduresmultiple-columns
Joey R
Joey R

Reputation: 23

SQL Server column split

I just started working with Queries in SQL so I'm not really experienced yet.

I tried to look this up, but I didn't understand most of it (and couldn't tell, if it really fits my problem), so I would love to have an explanation of what you would do to solve it and why!

I'm working with a database that sometimes handles data very inefficiently if you ask me.

Database structure is as follows:

USE [TestDatabase]
CREATE TABLE [dbo].[fruits](
...(other columns)
[diffruits1] int, NOT NULL
[diffruits2] [varchar](100) NULL,
...
)

This one column (diffruits2) would say something like that: 

"apples=1000, bananas=2, oranges=1, blueberries=102"

Now my goal is to use e.g. that 1000 value from apples (or 102 from blueberries) for an IF statement or other calculations. I thought converting from varchar to int is needed.

Like this: 

IF diffruits1=103
BEGIN

IF apples >= 1000
BEGIN
example.statement
END
IF blueberries =10
BEGIN
example.statement2
END

END

Something like that. I knew I could possibly split the column between '=' and ',' but honestly, I just don't know how. I want to use it for a procedure.

Upvotes: 2

Views: 176

Answers (2)

SqlZim
SqlZim

Reputation: 38023

This is similar to the answer by Zohar Peled, but I wanted to illustrate more on how you could use the splitting function in the procedure to run the conditions you wanted.

rextester demo: http://rextester.com/QXM5706

create table t (id int, diffruits2 varchar(8000));
insert into t values 
 (1, 'apples=1, bananas=2, oranges=3, blueberries=10')
,(2, 'apples=1000, bananas=2, oranges=1, blueberries=102');

      select 
          t.Id
        , Ordinal  = s.ItemNumber
        , Fruit    = ltrim(left(s.Item,charindex('=',s.Item)-1))
        , Quantity = stuff(s.Item,1,charindex('=',s.Item),'')
      from t
        cross apply dbo.delimitedsplit8K(diffruits2,', ') s
go

returns: 

+----+---------+-------------+----------+
| Id | Ordinal |    Fruit    | Quantity |
+----+---------+-------------+----------+
|  1 |       1 | apples      |        1 |
|  1 |       2 | bananas     |        2 |
|  1 |       3 | oranges     |        3 |
|  1 |       4 | blueberries |       10 |
|  2 |       1 | apples      |     1000 |
|  2 |       2 | bananas     |        2 |
|  2 |       3 | oranges     |        1 |
|  2 |       4 | blueberries |      102 |
+----+---------+-------------+----------+

Splitting strings reference:

And you would be able to use it in your procedures like so:

go
create procedure dbo.fruitful (@id int) as 
begin;
  set nocount, xact_abort on;
  select 
      t.Id
    , Ordinal  = s.ItemNumber
    , Fruit    = ltrim(left(s.Item,charindex('=',s.Item)-1))
    , Quantity = stuff(s.Item,1,charindex('=',s.Item),'')
  into #temp_fruit
  from t
    cross apply dbo.delimitedsplit8K(diffruits2,', ') s
  where t.id = @id;

  if exists (
    select 1 
    from #temp_fruit 
    where Fruit='apples'
      and Quantity>=1000
      )
  begin;
    /* steal an apple, or other code */
    select 1 as AppleStolen;
  end;

  if exists (
    select 1 
    from #temp_fruit 
    where Fruit='blueberries'
      and Quantity=10
      )
  begin;
    /* give them 2 more blueberries, just to be nice. or other code. */
    select 2 as BlueberriesAdded;
  end;
end;
go

The example procedure for the added string with id 1:

exec dbo.fruitful 1;

returns:

+------------------+
| BlueberriesAdded |
+------------------+
|                2 |
+------------------+

And for your original string:

exec dbo.fruitful 2;

returns:

+-------------+
| AppleStolen |
+-------------+
|           1 |
+-------------+

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

If you can't change the table structure, you can use a string splitting function to create a view that you can work with.

Create and populate sample table (Please save us this step in your future questions):

CREATE TABLE fruits
(
    diffruits1 int,
    diffruits2 varchar(100)
)

INSERT INTO fruits VALUES (1, 'apples=1000, bananas=2, oranges=1, blueberries=102')

If you are using sql-server 2016, you can use the built-in STRING_SPLIT function. For lower versions you need to create the function first.
For this answer I've chosed to use a function based on Jeff Moden's spliter, taken from Aaron Bertrand's article Split strings the right way – or the next best way:

CREATE FUNCTION dbo.SplitStrings
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;

Once you have the split string function, you can create the view like this:

CREATE VIEW vw_Splitted AS

SELECT  diffruits1,
        LTRIM(RTRIM(LEFT(Item, CHARINDEX('=', Item)-1))) As Name,  
        CAST(RIGHT(Item, LEN(Item) - CHARINDEX('=', Item)) As int) As Value
FROM fruits
CROSS APPLY dbo.SplitStrings(diffruits2, ',')

Test the view:

SELECT *
FROM vw_Splitted

Results:

diffruits1  Name    Value
1           apples  1000
1           bananas 2
1           oranges 1
1           blueberries 102

You can see a live demo on rextester:

Upvotes: 2

Related Questions