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.

View 5 Replies


ADVERTISEMENT

SQL Server 2012 :: Counting Characters In A String Before A Space

Jun 11, 2014

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.

View 9 Replies View Related

SQL Server 2012 :: Strip Non-numeric Characters From A String

Jul 15, 2015

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....

View 9 Replies View Related

SQL Server 2012 :: Removing Greek Characters From A String

Sep 17, 2015

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.

DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters

In this case, the "α" is being replaced, but so are "a" and "A".

I have tried changing the datatype from varchar to nvarchar and also changing the collation.

View 4 Replies View Related

SQL Server 2012 :: Replacing Recurring Characters In A String With Single Character

Jan 20, 2014

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.

View 9 Replies View Related

SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters

Jun 3, 2015

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'

View 8 Replies View Related

SQL 2012 :: Eliminate Characters From A String Based On Another String?

Dec 17, 2014

I have a string 'ACDIPFJZ'

In my table one of the column has data like

PFAG
ABCDEFHJMPUYZ
KML
JC
RPF

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.

My output should be:

PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)

View 2 Replies View Related

SQL 2012 :: How To Select Last Characters From A String

Jun 19, 2014

I have the following string and am trying to select only the characters before the last "</>". How can I do this?

declare @String varchar(500)
set @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>'

select REVERSE(substring(REVERSE(@String),5,charindex(':',REVERSE(@String))-5))Here is what I tried so far:

[Code] ...

View 4 Replies View Related

SQL 2012 :: Regular Expression For Capital Characters In String

Mar 1, 2014

I need to verify data in a column and do pattern matching on the string in each field.

I've create a CLR Function that will verify the element against the patter and return a True or Fales....

I have only used reg expressions once and am struggling mightly. I'm bacially here. A

I need to match a pattern that each word in the string will be a Capital letter.

ex. The beginning of the day - Fail
ex. The Beginning Of The Day - Pass

[URL] .....

View 2 Replies View Related

SQL 2012 :: Removing Consecutive Characters From Middle Of A String

Apr 14, 2015

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.

View 9 Replies View Related

SQL Server 2008 :: Extracting Numbers From String

Jun 2, 2015

Anyny in-built sql function that gives us numeric values in a string?

I have to deal with some inconsistent US phone numbers stored in DB. They are stored as

(xxx)xxx-xxxx
xxx xxxx xxxx
(xxx) xxx-xxxx
xxx-xxx-xxxx
xxxxxxxxxx

I don't want to apply nested REPLACE function to eliminate all unnecessary characters to get 10 digit number from DB.

View 9 Replies View Related

Extracting A Portion Of A String In SQL Server 2005

Apr 22, 2008

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) )

RETURN @str_String

END

Thanks in advance for the help

View 10 Replies View Related

SQL Server 2012 :: Extracting Data From XML Query

Sep 28, 2015

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.

create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">

[Code] ....

View 6 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

How To Remove Non-Numeric Or Non-alphameric Characters From A String In Sql Server 2005

May 16, 2007

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
 
 

View 2 Replies View Related

How To Remove Non-Numeric Or Non-alphameric Characters From A String In Sql Server 2005

May 16, 2007

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
 
 

View 2 Replies View Related

Extracting Part Of A String

Aug 22, 2006

Hello,

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!

View 5 Replies View Related

SQL Server Compact Edition Ntext Fails With String Over 4000 Characters

Jan 25, 2008

This is with SQLCe NET 3.5.0.0 running on Windows Server 2003 or Server 2008, not on a Windows mobile operating system.

The following code fails with ntext entries above 4000 characters:

Dim cn As New SqlCeConnection(ConnectString())
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
Dim info as string

info = "This is lengthy text".PadLeft(4200)
Dim cmd As SqlCeCommand

strSQL = "create table testTable ("
strSQL &= "docType nvarchar (50) NULL, "
strSQL &= "docFlag nvarchar(10) NULL, "
strSQL &= "docData ntext NULL, "
strSQL &= " )"

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

'---- insert a row into the testTable

strSQL = "INSERT INTO testTable ("
strSQL &= "docType, "
strSQL &= "docFlag, "
strSQL &= "docData, "
strSQL &= ") "
strSQL &= "VALUES ("
strSQL &= "@docType, "
strSQL &= "@docFlag, "
strSQL &= "@docData, "
strSQL &= ")"

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

View 3 Replies View Related

SQL Server 2012 :: Finding Longest String Within A String Field

Mar 20, 2014

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.

View 9 Replies View Related

Transact SQL :: Extracting A Word From String?

Jun 30, 2015

Is there a function / way to extract a word from a particular position in the String.

I want to extract a word which is in the 16th position. All words are separated by spaces.

View 6 Replies View Related

SQL Server 2008 :: String Break Into First Rows And Then Columns Based On Special Characters?

Jul 1, 2015

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.

View 2 Replies View Related

SQL Server 2012 :: Function To Remove Excess Characters

Mar 5, 2014

I am looking for a function or way to return only results which does not include appended characters to order numbers.

For instance, below is a list of order numbers. I only want the order number that is SO-123456

OrderNumbers
SO-123456
SO-123456-01
SO-123456-2
SO-123457
SO-123457-1
SO-123457-02
SO-123458

I would like my query to only show the below results

SO-123456
SO-123457
SO-123458

What functions or query methods could achieve this?

I was hoping for something similar to RTRIM but that is only specific to white space.

View 9 Replies View Related

SQL Server 2012 :: Unable To Add More Than Around 4000 Characters To A Job Step

Apr 29, 2015

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.

View 9 Replies View Related

SQL Server 2012 :: Compare Characters Between 2 Strings And Eliminate Similarities

Oct 13, 2015

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.

View 4 Replies View Related

Transact SQL :: Replace Special Characters In ORACLE Or SERVER 2012?

Jun 8, 2015

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 ?

View 5 Replies View Related

SQL Server 2008 :: Normalizing Data Prior To Migration (Update String To Remove Special Characters)

Aug 21, 2015

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.

Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16

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.

View 3 Replies View Related

SQL 2012 :: Extracting Week-day Name From Date?

Mar 7, 2014

I want to extract day of the week from given date.

If the given date is 07-03-2014(dd-mm-yyyy).IF i run the query the output should be =Friday

View 2 Replies View Related

SQL 2012 :: Extracting Node From A Column In A Table That Contains XML

Dec 4, 2014

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

View 3 Replies View Related

SQL 2012 :: Extracting Data From Compressed CLOB XML Using SSIS?

Apr 14, 2014

Source System : Oracle

Target System : Ms Sql Server 2012

ETL Tool Used : SSIS

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 .

View 2 Replies View Related

How Can Get 10,000 Characters In A String Varaible

Jul 7, 2006

Hi

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.

Thanks

View 1 Replies View Related

Unlimited Characters In A String?

Jan 24, 2005

Is there a way to make a string field that has an unlimited amount of characters?

View 1 Replies View Related

Selecting The Last 3 Characters From A String

Jan 17, 2006

Hello Everyone,

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?

View 3 Replies View Related

How To Remove Characters In String

Jun 20, 2008

There are unwanted characters(''','/','&'.. etc) in column.
I need to remove these characters

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved