How to split string with inconsistent order format in SQL
sql split string by delimiter
sql split string by character
split comma separated string in sql server 2012
sql split string and get first element
'string_split' is not a recognized built-in function name.
sql split delimited string into rows
sql split string by space
I want to split the strings in the 'Scorer' column so that the scorer name is retained but not the score type (i.e. to remove the text within the brackets and the brackets to just leave the scorer name in that field).
Scorer Ellis J.(Conversion Goal) Ellis J.(Try) Ellis J.(Conversion Goal) Trueman J.(Try) (Conversion Goal)Brough D. (Try)McGillvary J. (Try)McGillvary J. (Penalty Goal)Brough D. Ellis J.(Conversion Goal)
It should look like the below.
Scorer Ellis J. Ellis J. Ellis J. Trueman J. Brough D. McGillvary J. McGillvary J. Brough D. Ellis J.
The correct solution would be to fix the database structure by adding another column to the table for the score type. In fact, you should probably have a table for score types and add a foreign key to it from this table.
Assuming you can't change the database structure, this is better done at the presentation layer. Any programming language should enable you do do it quite easily. String manipulation is not SQL's strong suit.
That being said, it can certainly be done using pure T-SQL - with a simple common table expression to get the brackets indexes using
charindex, and a case expression with
stuff in the select statement.
First, create and populate sample table (Please save us this step in your future questions):
DECLARE @T AS TABLE ( Scorer nvarchar(100) ); INSERT INTO @T (Scorer) VALUES ('Ellis J.(Conversion Goal)'), ('Ellis J.(Try)'), ('Ellis J.(Conversion Goal)'), ('Trueman J.(Try)'), ('(Conversion Goal)Brough D.'), ('(Try)McGillvary J.'), ('(Try)McGillvary J.'), ('(Penalty Goal)Brough D.'), ('Ellis J.(Conversion Goal)'), -- Note: I've added some edge cases to the sample data: ('a row with (brackets) in the middle'), ('Just an open bracket (forgot to close '), ('Just a close bracket forgot to open)'), ('no brackets at all'), ('brackets ) in reversed order (');
Then, the CTE:
WITH CTE AS ( SELECT Scorer, CHARINDEX('(', Scorer) As OpenBrackets, CHARINDEX(')', Scorer) As CloseBrackets FROM @T )
The select statement:
SELECT CASE WHEN OpenBrackets > 0 AND CloseBrackets > OpenBrackets THEN STUFF(Scorer, OpenBrackets, CloseBrackets - OpenBrackets + 1, '') ELSE Scorer END As Scorer FROM CTE
Scorer Ellis J. Ellis J. Ellis J. Trueman J. Brough D. McGillvary J. McGillvary J. Brough D. Ellis J. a row with in the middle Just an open bracket (forgot to close Just a close bracket forgot to open) no brackets at all brackets ) in reversed order (
Splitting Strings: How to Work With Consistent Data in SQL Server , strange. For example, I'm working with a client where they provide me with an Excel spreadsheet of rows and want to import the data into SQL STRING_SPLIT – Examples. Demonstrating STRING_SPLIT function with a few helpful examples here. 1. STRING_SPLIT – Split Delimited List In a Variable @PlayerNames variable stores the list of player names separated by a comma delimiter. Furthermore, using the following query, we are splitting comma delimited list into a single column table
Below query works for you
SELECT LTRIM(RTRIM(REPLACE(Scorer, SUBSTRING(Scorer, CHARINDEX('(', Scorer), CHARINDEX(')', Scorer) - CHARINDEX('(', Scorer) + 1), ''))) FROM <TABLENAME>
Split strings the right way - or the next best way, I know many people are bored of the "split strings" problem, but it still seems the input – for example it is possible to use for comma-separated lists of integers or GUIDs. Numbers ORDER BY Number ), cteStart(N1) AS ( SELECT t. Numbers Table results seems to remain consistent (albeit stats are not The STRING_SPLIT() function returns a single-column table, whose column name is value. This result table contains rows which are the substrings. Note that the values in the value column can be in any sort order. To get the expected order, you need to add the ORDER BY clause to the SELECT statement:
These two pieces of information (the name and action) should not be in the same column. You should create a separate column for name and for action. And if the position of the action (before or after the name) is important, you might even need an additional column for that.
When you have migrated your data after that - in other words when you have cleaned up - you could still create a view or a computed column to output the
scorer the way you do now, for example
ALTER TABLE my_table ADD scorer AS athlete_name + ' (' + action + ')'
Splitting Strings in SQL Server 2016 – SQLServerCentral, First up, let's try splitting a string with a separator greater than one character. In this example, I'll send the results to a temp table, and then query the This is consistent with the way that SQL handles a NULL in the OPENXML rows in the test table ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT if the values in column 1 are always one character long, and the values in column 2 are always 2, you can use the SQL Left and SQL Right functions: SELECT LEFT(data, 1) col1, RIGHT(data, 2) col2 FROM <table_name>
You could try:
SELECT Scorer ,CASE WHEN PATINDEX('%(%)%',Scorer) > 1 THEN LEFT(Scorer, PATINDEX('%(%)%',Scorer)-1) ELSE RIGHT (Scorer, LEN(Scorer) - CHARINDEX(')',Scorer,1) ) END AS ColumnName FROM ScoreTable
this should work assuming you only expect 1 instance if the pattern per row, but will work whether the "()" data is at the front or the back of the values
Parsing and rotating delimited data in SQL Server, This applies to SQL Server 2012 and higher. Server four-part object names – it will parse any function or string data that is We should now have the PhoneNumbers field values returned in this format: [main number]. ORDER BY ID; let's spend some time cleaning up the inconsistencies in the data. String manipulation is not SQL's strong suit. That being said, it can certainly be done using pure T-SQL - with a simple common table expression to get the brackets indexes using charindex , and a case expression with stuff in the select statement.
You can use this query
with t(str) as ( select 'Ellis J.(Conversion Goal)' union all select '(Conversion Goal)Brough D.' union all select ' (Try)McGillvary J.' ) select (case when charindex('(', ltrim(str)) = 1 then substring(str,charindex(')', str)+1,len(str)) else left(str, charindex('(', str) - 1) end) as "Scorers" from t Scorers -------------- Ellis J. Brough D. McGillvary J.
by contribution of
left functions together.
ltrim is used against probabilty of spaces left before
( character at the beginning of the string.
SQL Server STRING_SPLIT Function, The STRING_SPLIT() function is a table-valued function that splits a string into a order, you need to add the ORDER BY clause to the SELECT statement: This example uses the STRING_SPLIT() function to parse a comma-separated list of SQL. DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike' SELECT value FROM STRING_SPLIT (@tags, ',') WHERE RTRIM(value) <> ''; STRING_SPLIT will return empty string if there is nothing between separator. Condition RTRIM (value) <> '' will remove empty tokens.
T-SQL: Using Bulk Insert to Import Inconsistent Data Format , In order to use bulk insert directly, we have to make sure that all the data is If our data had "a consistent format" then all string data would be After we found a way to parse the data, we can use a simple SELECT INTO In SQL Server 2016, Microsoft introduced the STRING_SPLIT () inbuilt function to split a string using a specific delimiter. This is the easiest method to split delimited string in SQL Server. To use STRING_SPLIT, the database should be at least in compatibility level of 130. Check the compatibility of your database before using this function.
How To Split A Comma Separated String Into Multiple Column In , Here's an example, showing a comma separated list of ordered employee names How to split comma-delimited string column into multiple records in SQL Server I have a SQL report which pulls a list of orders. I have >200k rows that need to be split in this manner but as you can see below, it's inconsistent how many USE MSSQLTipsDemo GO CREATE FUNCTION [dbo].[SplitString] ( @String NVARCHAR(4000), @Delimiter NCHAR(1) ) RETURNS TABLE AS RETURN ( WITH Split(stpos,endpos) AS( SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos UNION ALL SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1) FROM Split WHERE endpos > 0 ) SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), 'Value' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos) FROM Split )
- Welcomde to stackoverflow. Please take a minute to take the tour, especially How to Ask. Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this.
- the solution assumes the scorer's name will be entirely to the left or right of the "(%)" pattern. you may need to tweak the code in the expression slightly to accommodate your case