-1

In gallons_column I sometimes encounter strings like ‘98.0,2.45,0.25’.I have been able to extract 98.0 and 0.25 but the middle string I am having difficulties. I have been trying substring to extract the middle string but I can’t work out the rest of the syntax.

Any help or suggestions will be appreciated. Thanks

I am using SSM 18. I used the code below to extract 98.0 and 0.25

volume_sold_liters=LEFT(volume_sold_gallons,CHARINDEX(',',volume_sold_gallons)-1),
volume_sold_gallons = RIGHT(volume_sold_gallons,CHARINDEX(',', (REVERSE(volume_sold_gallons)))-1)
FROM project
WHERE store_number =3621
```````
--As for the middle string I was trying to use the code below with no success.
`````````
SELECT volume_sold_liters = SubString(volume_sold_gallons,CHARINDEX(',',volume_sold_gallons)+1),
RIGHT(volume_sold_gallons,CHARINDEX(',', (REVERSE(volume_sold_gallons)))-1) FROM project
Daniel Ramirez
  • 1
  • 1
  • Is this a one off or are you doing this on millions at a time? If one off you can use string parse (to turn a string into a table) and select the value from there. (depending SQL version may be built in. Do you have table structure and examples or does this one cover every possible example? – Brad Jun 30 '21 at 18:15
  • 1
    Can you use [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15)? – Gert Arnold Jun 30 '21 at 18:19
  • Add the code you've tried and your SQL Server version to your question. – Dan Guzman Jun 30 '21 at 18:19
  • Thanks for answering my question. It occurs about 20 times in a column. I want the middle string to go into a different column. I haven’t tried parse let me give a try. – Daniel Ramirez Jun 30 '21 at 18:22
  • Fix the design, that is the *real* solution here. Clearly your values are numerical, yet you are storing them as a delimited `varchar`. This breaks 2 of the basic principles of *good* database design: 1. That a single value should represent **one** atomic value. 2. That data should be stored in a suitable strongly type data type. – Thom A Jun 30 '21 at 18:37
  • Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. – HABO Jun 30 '21 at 18:45
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) – Yitzhak Khabinsky Jun 30 '21 at 18:45
  • Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) – Charlieface Jun 30 '21 at 19:46

1 Answers1

2

With a bit of JSON, it is a small matter to parse a string into columns

The pattern is pretty clear, so you can expand as needed.

Example

Declare @YourTable Table ([gallons] varchar(50))
Insert Into @YourTable Values 
 ('98.0,2.45,0.25')
 
Select A.* 
      ,Pos1  = JSON_VALUE(S,'$[0]')
      ,Pos2  = JSON_VALUE(S,'$[1]')
      ,Pos3  = JSON_VALUE(S,'$[2]')
From @YourTable A
Cross Apply ( values ( '["'+replace([gallons],',','","')+'"]' ) ) B(S)

Results

gallons         Pos1    Pos2    Pos3
98.0,2.45,0.25  98.0    2.45    0.25
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Be careful using json_value - it returns an nvarchar(4000) and could cause issues with different data types or truncation if the string is varchar and more than 4000 characters. – Jeff Jun 30 '21 at 22:23
  • @Jeff Valid point, but I really don't think that is a concern here. – John Cappelletti Jun 30 '21 at 22:44