-1

In a Select statement, I need to strip leading and trailing commas from one column. It has values like:

,,Value1,Value2,,,,

There are no spaces between the commas.

I have tried using CharIndex to find the first occurrence of [a-zA-Z] but I don't know how to make that work.

Here's an example of my Code:

Select CustID, SaleDate, TargetColumn, CharINDEX('a-z', TargetColumn) [Position]

Every row in the [Position] column returns a 0.

I was hoping to be able to locate the first and last instance of [a-zA-Z] and then remove everything before and after those positions, respectively.

I appreciate that this may have been asked before, but I was not able to find tsql code that I can use in a Select statement.

I'd appreciate any help/pointers.

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Talay
  • 349
  • 1
  • 5
  • 17
  • I'm sorry. The column in question can also have a string of commas between actual values, Eg: ,Food Resources,,,,,,,,,Department of Human Services,, The string is created by me in SQL that concatenates values from multiple columns and puts an empty string followed by a comma when no value is found for a particular column. Perhaps that is where I should do the 'cleanup'. – Talay Jul 19 '21 at 23:11
  • 3
    To answer the question [as stated](https://meta.stackexchange.com/q/66377/147640), https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver15 – GSerg Jul 19 '21 at 23:13
  • What makes you think that "A character expression containing the **sequence** to find." _(Emphasis mine.)_ as documented for [`CharIndex`](https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql?view=sql-server-ver15#arguments) _expressionToFind_ means that the _sequence_ might be interpreted as a _pattern_ when that is what you prefer? – HABO Jul 19 '21 at 23:34
  • @Talay Can you edit the Question and add your expected outcome please. As below Answer shows, You can get the index of the [a-z][A-Z] using PATINDEX() But not sure what result you want to see. If you provide the expected outcome, we can come up with solutions. Thanks – Gudwlk Jul 20 '21 at 06:02
  • The correct answer would be that you shouldn't mess around with delimited strings in the first place. Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Aug 02 '21 at 06:49

2 Answers2

1

If you ... need to strip leading and trailing commas from one column ... and you use SQL Server 2017+, TRIM() is an option:

SELECT TRIM(',' FROM TargetColumn) AS TargetColumn
FROM (VALUES (',,Value1,Value2,,,,')) t (TargetColumn)

But, if you want to make more complicated parsing (as mentioned in the comments ... the column in question can also have a string of commas between actual values ...), a JSON-based approach (with appropriate transformations and string splitting) is a possible solution (SQL Server 2016+ is needed). The idea is to split the text, remove the empty substrings and then aggegate the parts again:

SELECT TargetColumn = (
   SELECT STRING_AGG([value], ',') WITHIN GROUP (ORDER BY CONVERT(int, [key]))
   FROM OPENJSON(CONCAT('["', REPLACE(TargetColumn, ',', '","'), '"]'))
   WHERE [value] <> ''
)
FROM (VALUES 
  (',,Value1,Value2,,,,'),
  ('Food Resources,,,,,,,,,Department'),
  ('No commas')
) t (TargetColumn)

Result:

TargetColumn
-------------------------
Value1,Value2
Food Resources,Department
No commas
Zhorov
  • 28,486
  • 6
  • 27
  • 52
0

For the first occurrence of A-Z you need ‘PATINDEX’ not ‘CHARINDEX’.

PATINDEX('%[a-z]%' TargetColumn)

To find the last position you could go with

LEN(TargetColumn)-PATINDEX('%[^,]%', REVERSE(TargetColumn))
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18