SQL Server 2012 :: Extracting String Between Certain Characters
Aug 18, 2014
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117
CA132
OH174
MD163
FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
I am trying to count the characters in a sting before a space. Here is the example of what I am trying to accomplish.
"2073 9187463 2700' 4 7 4, the string character count is 4 before the space, 7 is the count before the next space and the last is the last in the string, if there was more characters within this string for example....'2073 9187463 2700 7023 6044567' it would return the number of characters in the string before the space and at the very end of it.
I am looking for the fastest way to strip non-numeric characters from a string.
I have a user database that has a column (USER_TELNO) in which the user can drop a telephone number (for example '+31 (0)12-123 456'). An extra computed column (FORMATTED_TELNO) should contain the formatted telephone number (31012123456 in the example)
Note: the column FORMATTED_TELNO must be indexed, so the UDF in the computed column has WITH SCHEMABINDING.... I think this implicates that a CLR call won't work....
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255) SET @code = '12333345566689'; SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN*** Example Record 2. Theale, Nr Reading, Berkshire Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0 THEN LEN(Address ) ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0 THEN LEN(Address) ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex: String value is 10 characters long. The string value is X000001250. The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
Hello, this seems simple but I've been banging my head a while.
I have a data field that is formated like this: NYT/2000-Subways/7510-Electronics Mtc/7540-Data Svcs.
I need to pull out the string after the second / and before the third / in this case the value is 7510-Electronics Mtc but it does change.
I have this function that returns the first value (NYT):
USE [Data_Warehouse] GO /****** Object: UserDefinedFunction [dbo].[Get_Dept] Script Date: 04/22/2008 09:22:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
-- Provide the phrase before (ex. 'Worklog Related to'), the field searched (ex. UPDATE_ACTION), and -- the phrase after (ex. ']'). -- This function returns the string found after any phrase and before any phrase, within any field searched.
ALTER FUNCTION [dbo].[Get_Dept](@in_Phrase_Before varchar(250),@in_Search_Field varchar(250)) RETURNS varchar(250) AS BEGIN Declare @str_String As varchar(250) Declare @tmp_String as varchar(250) Set @str_String = null -- to clear the variable buffer
Set @in_Phrase_Before=char(37)+(LTrim(Rtrim(@in_Phrase_Before)))+char(37) -- if 'Null', all strings will be returned
If Left(@in_Search_Field,4) = 'NYT/' BEGIN Set @tmp_String = Right(@in_Search_Field,(len(@in_Search_Field) - 4 ) ) Set @in_Search_Field = @tmp_String END
If (PatIndex(@in_Phrase_Before, @in_Search_Field)) > 0 --Checks to see if the phrase before is in the search field Set @str_String=SubString(@in_Search_Field,1, (PatIndex(@in_Phrase_Before, @in_Search_Field)-1) )
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
Hi to all, I am having a string like (234) 522-4342. i have to remove the non numeric characters from the above string. Please help me in this regards. Thanks in advance. M.ArulMani
Hi to all, I am having a string like (234) 522-4342. i have to remove the non numeric characters from the above string. Please help me in this regards. Thanks in advance. M.ArulMani
I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example:
"the text looks like this but has two numbers 55555/66666 within the string"
I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!
cmd = New SqlCeCommand(strSQL, cn) Try cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message & vbNewLine & strSQL, "Table Error 7", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 8", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try If cn.State = ConnectionState.Closed Then cn.Open() End If
Try cmd = New SqlCeCommand(strSQL, cn) cmd.Parameters.AddWithValue("@docType", "a type") cmd.Parameters.AddWithValue("@docFlag", "a flag") cmd.Parameters.AddWithValue("@docData", info) cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try End If
Changing the cmd.Parameters as follows works:
Dim paramdocData As SqlCeParameter Try cmd = New SqlCeCommand(strSQL, cn) cmd.Parameters.AddWithValue("@docType", "a type") cmd.Parameters.AddWithValue("@docFlag", "a flag") paramdocData = cmd.Parameters.Add("docData", SqlDbType.NText) paramdocData.Value = info cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try
Thanks to the following Microsoft ReadMe for the above suggestion. See: http://download.microsoft.com/download/f/7/2/f72ebbf8-4df1-4800-b4db-c2405c10d937/ReadmeSSC35.htm
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment]( [EQU] [VARCHAR](50) NOT NULL, [Notes] [TEXT] NULL, [Facility] [VARCHAR](50) NULL) INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility]) SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA' SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
SQL Server 2012 SP2 Enterprise Edition (11.0.5058.0) on Windows Server 2008 R2
At some point a few months ago we encountered an issue where we hit some size limit on the amount of text we could enter into a Transact-SQL step of an Agent job. Attempting to create a job like this with sp_add_job will produce the error
Msg 50000, Level 16, State 10, Procedure sp_add_jobstep_internal, Line 255 String or binary data would be truncated.
Adding the job step via SSMS yields
Alter failed for JobStep 'xxx'. (Microsoft.SqlServer.Smo) Additional information: An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) String or binary data would be truncated. The statement has been terminated. (Microsoft SQL Server, Error: 8152)
I've checked sp_add_jobstep_internal, sp_add_jobstep and the sysjobsteps table and all references to the command field are nvarchar(max). We can run the same job creation code without error on a SQL Server 2008 R2 Enterprise Edition machine and two SQL Server 2012 SP2 Developer Edition boxes. All our 2012 servers were fresh installs, not upgrades.
I am trying to write a function to compare the characters between 2 strings and eliminate the similarities to be able to return at the end the number of differences between them.
Having in mind i need the bigger number of differences to be returned also if a character is repeated in one of the 2 words it will be eliminated once because it exist only one time in other string.
I will give an example below to be more clear
--Start declare @string1 as varchar(50)='imos' declare @string2 as varchar(50)='nasos'; WITH n (n) AS ( SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
[Code] ....
The differences in first string from second one are 2 (i,m) while the differences in second string from first one are 3(nas). So the function should return 3 in previous example.
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
I am trying to extract what I believe is called a Node from a column in a table that contains XML. What's the best way to do this? It is pretty straightforward since I'm not even looking to include a WHERE clause.
What I have so far is:
SELECT CCH.OrderID, CCH.CCXML.query('/cc/auth') FROM tblCCH AS CCH
My source data is present in XML File which is stored in CLOB column Of Oracle. CLOB column is compressed.I need to Migrate data by Uncompressing XML to SQL 2012 .
Do I need to define XML column in SQL Server 2012 for storing Uncompressed CLOB values ?
How to uncompress the clob and extract the required data from XML using SSIS .
I am using nvarchar(MAX) string variable. But its length is maximum upto 8,000 charaters. But I want to assign 10,000 characters. So how can I get this.
I am trying to select the last 3 characters from a string. I am running into problems because the sting that I am selecting from are not the same amout of characters.
For example:
Item
abc145264 efg1254 wqx21456
How would I be able to select the last three characters from a list that could have more than 50 variations on the number of characters.
I tryed right(item, 3) but that does not work because all the lenghts are different. Any ideas?
