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


ADVERTISEMENT

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

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 Server 2012 :: Filter Duplicates Based On String?

Feb 19, 2014

CREATE TABLE #Names
( ID INT IDENTITY(1,1),
NAME VARCHAR(100)
)
INSERT INTO #Names VALUES ('S-SQLXX')
INSERT INTO #Names VALUES ('S-SQLXX.NA.SN.ORG')
INSERT INTO #Names VALUES ('S-SQLYY')
INSERT INTO #Names VALUES ('S-SQLYY.NA.SN.ORG')
INSERT INTO #Names VALUES ('S-SQLCL-HR')
INSERT INTO #Names VALUES ('S-SQLCL-MIS')
SELECT * FROM #Names

--I want to filter out S-SQLXX.NA.SN.ORG because S-SQLXX.NA.SN.ORG is a duplicate of S-SQLXX eliminating .NA.SN.ORG from it.

--I want to filter out S-SQLYY.NA.SN.ORG because S-SQLYY.NA.SN.ORG is a duplicate of S-SQLYY eliminating .NA.SN.ORG from it.

--However I want to keep S-SQLCL-HR and S-SQLCL-MIS in my list of names as they do not have .NA.SN.ORG as a part of their name

--I want ONLY these returned IN the SELECT

SELECT * FROM #Names WHERE ID IN (1,3,5,6)
DROP TABLE #Names

View 1 Replies View Related

SQL Server 2012 :: Split String Into Columns Based On Special Character

Dec 4, 2013

How to get the required result in SQL 2012

Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')

My Expected Output

Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS

View 1 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

SQL 2012 :: Picking Number String Out Of Text String

Jul 14, 2015

I have a text field which has entries of variable length of the form:

"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"

I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:

1234
123
12389

the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.

View 5 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

Strip Characters Out Of String

Jan 11, 2002

I have a phone number string (416) 555-5555 in a table. I'd like to perform a search on the string so that the user is able to pass any number, and the query returns all phone numbers like it. What I'd like to do is to strip out the brackets and dashes and perform a like search.

View 4 Replies View Related

Grabbing Characters From A String

Jul 23, 2005

HelloI want to write a stored procedure (using Enterprise Manager) that can grabthe digits that are inbetween the two dashes (-) in strings like:123-150-401-123-832-4215-61The digits to the left, right and inbetween the dashes could be any length,so a static "get the 5th, 6th and 7th digit" stored procedure won't work.Many thanks,--Chris Michaelwww.INTOmobiles.comDownload 100s of ringtones, wallpapers & logos every month for only £1.50per week

View 1 Replies View Related

Count SPECIFIC Characters In A String

Dec 30, 2003

How do I count the number of specific characters in a string ?

Example:

declare @var as varchar(50)
set @var="1abc1efg1"


If I wanted to count the "1"...I'll get "3" for answer.
This could maybe done by using a while loop, but is there any T-SQL command for this?????

View 1 Replies View Related

Pull Characters Up To 3rd Space In String

Mar 19, 2015

I am trying to figure out how to pull only a certain character string.

orig string varies in length (Varchar 50)
ex: MID - Proposed - Prime
ex: MID - NotProposed - NotPrime

I want to keep the first 6 characters 'MID - '
as well as up to the 3rd SPACE (not including the space)

so
ex: MID - Proposed
ex: MID - NotProposed

View 1 Replies View Related

Optional Characters In Search String

Jun 21, 2006

I'm trying to search for commonly abbreviated company titles (ie limited, partnership, and so on). I would like to make my sql statement as short as possible (it's already quite lengthy as is). But I'm having trouble netting the abbreviated forms such as LTD and LMTD for limited (I have no control over the data I get, it comes from different counties with no standardization). I've tried using braketted strings like "L[I,IMI,M,]T[ED,D,]" and all other combinations I can think of, including using single quotes in the each string, and removing the empty placeholder and still can grab all instances.

Someone else's insight would be appreciated.

View 4 Replies View Related

SQL Select Command.. Allow X Amount Of Characters From A String

Jan 17, 2008

Ok so we gotSELECT this, that, others FROM some.database WHERE this=@this So in the database the others field is a string that can have up to 200 characters, but on this particular data pull I only want to pull the first 50 characters of the others field.  How can I do that?  Thanks.     

View 4 Replies View Related

Removing Special Characters From A Number String.

Jul 11, 2001

Hi,

I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.

Thanks, Mark

View 1 Replies View Related

Stripping Non-Numeric Characters From A String Function

Jul 9, 2002

Hi,

As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.

In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.

Are there any other more efficient ways of going about this?

View 4 Replies View Related

Handling Wildcard Characters In Query String

Nov 11, 2005

Hi

First interaction to the forum.
My Query is :

I had a User Management module in my application where I created a user with name

`~!@#$@%^&*()[_]+|}{":?><-=[[]];',./

Now I have a functionality to search for the user existing. For that give the search string or a single character and it finds out all the records containing the character.

How do I go about it as the SP i created for it gives correct results except the following

1. Search for % - Gives all record
2. Search for _ - Gives all records
3. Search for [ - Gives NO record
4. Search for the whole string - Gives NO Record

I handeled a few issues

1. replaced [ by [[]
2. replaced _ by [_]

So issues 2 & 3 are resolved.

Tried replacing % by [%] but did not work


Could someone plz help

Thanks in advance
Ashutosh

View 11 Replies View Related

Escaping International (unicode) Characters In String

May 29, 2007

Y'all:I am needing some way, in the SQL Server dialect of SQL, to escape unicodecode points that are embedded within an nvarchar string in a SQL script,e.g. in Java I can do:String str = "This is au1245 test.";in Oracle's SQL dialect, it appears that I can accomplish the same thing:INSERT INTO TEST_TABLE (TEST_COLUMN) VALUES ('This is a1245 test.");I've googled and researched through the MSDN, and haven't discovered asimilar construct in SQL Server. I am already aware of the UNISTR()function, and the NCHAR() function, but those aren't going to work well ifthere are more than a few international characters embedded within astring.Does anyone have a better suggestion?Thanks muchly!GRB-----------------------------------------------------------------------Greg R. Broderick Join Bytes!A. Top posters.Q. What is the most annoying thing on Usenet?---------------------------------------------------------------------

View 3 Replies View Related

Transact SQL :: Strip Non-numeric Characters From String

Jul 27, 2015

If I have a string with the following values, I’d like to replace the non-numeric characters with a space.

Input
01234-987
012345678
01234 ext 65656
Tel 0123456
012345 898989

Output
01234 987
012345678
01234 65656
0123456
012345 898989

View 20 Replies View Related

Return Only Numeric Characters From A String Column

Dec 4, 2007

I have written this query to return only numeric characters from a string.

select *
from TableA
where isNumeric(Column_A) = 1

But I have discovered that the following

SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')

all return a value of 1. I do not want these in my result set.

If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.

I would like a simple way of only returning numbers in my resultset.

Thanks for looking at this.

View 2 Replies View Related

Search String Field (nvarchar) For Unicode Characters

Nov 8, 2006

MS SQL 2000. Does anyone know how to find all rows where an nvarchar column contains a specific unicode character? Is it possible without creating a user defined function? Here's the issue. I have a table Expression (ExpID, ExpText) with values like 'x < 100' and 'y ≤ 200'. where the second example contains Unicode character 8804 [that is, nchar(8804)]. Because it's unicode, I don't seem to be able to search for it with LIKE or PATINDEX. These fail:
SELECT * FROM Expression WHERE ExpText LIKE '%≤%'   -- no recordsSELECT * FROM Expression WHERE PATINDEX('%≤%', ExpText)   -- no records
However, SELECT PATINDEX('%≤%', 'y ≤ 200')  will return 3.
Any suggestions? Thanks in advance.

View 3 Replies View Related







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