SQL Server 2012 :: More Than 1 Alphanumeric Chars In A String

Mar 12, 2014

I have an employee table

Surname GivenName
ABC x.yz
A.BC X.*YZ
A*.BC xyz

The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters. In this case the output will be

a.bc x.*yz
a*.BC xyz

I know how to find non alphanumeric chars in a string but not sure if I can how to find the strings with minimum 2 non alphanumeric chars?

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Alphanumeric Range Grouping?

Feb 25, 2015

I am having some difficulty getting a query to output an alpha numeric range grouping.

I have this data set:

Despatch_id Sample_ID

MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757

[code]....

Output:

DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813

They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.

I have included the create table scripts and example data below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL

[code]....

View 7 Replies View Related

SQL Server 2012 :: Auto Incrementing Alphanumeric Field In Table

Oct 1, 2014

I need to auto increment an alphanumeric field in a table.

The process is as follows:

1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659

View 9 Replies View Related

SQL Server 2012 :: More Than 8000 Chars In A Column

Jan 30, 2014

Is there any possibility to store more then 8000 chars in a column?

View 1 Replies View Related

SQL Server 2012 :: Sorting By Alphanumeric Table Column Of NVARCHAR Datatype

Sep 24, 2015

I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype. The sample data is given below:

CREATE TABLE #Activities(activityName NVARCHAR(100))

INSERT INTO #Activities VALUES('Field phase S14-04932-01')
INSERT INTO #Activities VALUES('Phase reporting')
INSERT INTO #Activities VALUES('Phase running')
INSERT INTO #Activities VALUES('RD1')

[Code] ....

The output of the query is like this:

A1
A2
A3
A4
E1 0DAA1
E10
E2 0DAA2

[Code] .....

The output what I require is this:

A1
A2
A3
A4
E1 0DAA1
E2 0DAA2

[Code] ....

View 9 Replies View Related

SQL Server 2012 :: Remove Non Alpha Chars From A Column

Mar 1, 2014

How to remove non alpha chars from a column ?

I googled the following code
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

[code]....

View 9 Replies View Related

Inserting Long String Cuts Of At 150 Chars

May 18, 2006

Hi there, I'm having a problem when I insert a string (from C#) which is 167 characters long. The field in SQL Server Express is a varchar(250), but the string gets cut of somehow at 150 characters.

Why does this happen?

The table and stored proc I use are defined like this:

USE [Test]
GO
/****** Object: Table [dbo].[tblImage] Script Date: 05/18/2006 10:42:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblImage](
[imgID] [bigint] IDENTITY(1,1) NOT NULL,
[persID] [bigint] NOT NULL,
[imgPad] [varchar](250) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



ALTER PROCEDURE [dbo].[spInsertImage]

@persID BIGINT

, @imgPad VARCHAR(150)





AS

BEGIN

SET NOCOUNT ON;

INSERT INTO tblImage ( persID

, imgPad )

VALUES ( @persID

, @imgPad)

END

Does anybody know what is going on?

Thanks,

Starik

View 3 Replies View Related

Can You Parse Out A Text String &> 8000 Chars Long??

Mar 30, 2005

Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.

Say I have this string that is text data type...

'aaa|bbb|ccc|ddd|....'

..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.

View 3 Replies View Related

Stripping Non-numeric Chars From A Field String - SQL 2000

Jul 20, 2005

Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))

View 1 Replies View Related

SQL 2012 :: Create Random Alphanumeric Characters For Primary Key Values

Feb 11, 2015

For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.

eg: cmSbXsFE3l8

It can start from 4 digit characters and can grow to 6, 7 as required

The database will involve more than 10000 concurrent users.

I don't want to use guid or auto increment integer or sequence.

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

TSQL - Select First 3 Chars Where Not Special Chars

Feb 5, 2002

Say I have a column called 'NAME' in a table called 'CLIENT' and the values in NAME are Surnames or company names like:

NAME
----------------------
1-FOR-ALL
A.B. SMITH (TOOLS LTD)
BROWN
THOMSON
VW CAR SALES



I want my select to return the first 3 characters, excluding special characters (only characters between 1 and z).

In example, the following would be returned for the data above:

NAME
----------------------
1FO
ABS
BRO
THO
VWC

View 1 Replies View Related

TSQL - Select First 3 Chars Where Not Special Chars

Feb 11, 2002

Say I have a column called 'NAME' in a table called 'CLIENT' and the values in NAME are Surnames or company names like:

NAME
----------------------
1-FOR-ALL
A.B. SMITH (TOOLS LTD)
BROWN
THOMSON
VW CAR SALES



I want my select to return the first 3 characters, excluding special characters (only characters between 1 and z).

In example, the following would be returned for the data above:

NAME
----------------------
1FO
ABS
BRO
THO
VWC

View 1 Replies View Related

Non English Chars Are Being Shown As Junk Chars

Jan 9, 2008

Hi All

I have loaded some data to the application using flat files

which has non english chars.

all the columns in the database are NVARCHAR type.

but in db and in application UI, the non english chars are being diplayed as junk chars. ???121

The application supports UTF-8 format

is there any setting at db level to be modified to display the non english char set as is.

Thanks

View 2 Replies View Related

Truncation Error: 255 Chars To 2 Chars.

Aug 27, 2007

Hi,

I am very new to using SSIS.

Trying to import data from MS Access 2000.

I receive the error "
[OLE DB Destination [1907]] Warning: Truncation may occur due to inserting data from data flow column "GENDER" with a length of 255 to database column "GENDER" with a length of 2. " on the source flow.

I have done some googling and came up with this post: http://torontosql.dotnetnuke-portal.com/Default.aspx?tabid=32233 which I thought may help, but it does not.

The query against the access datasource features the column: iif([sex]=1, 'm', 'f'). I tried using left(..., 2) as well, but SSIS is determind to treat the field as 255 characters for some reason.

I don't even particualrly care that the field is 255 chars and the sources is only two, I just want the data in! I have other fields coming up with similar error.

Can someone please advise?

PS, what is th significance of the "External Columns" Vs "Output Columns" on the Input and Output Properties tab in Advanced Editor?

I am really struggling with SSIS, it is not as intuitive as DTS.


View 1 Replies View Related

Ntext Over 4000 Chars Causes 'Data In Row (n) Was Not Update... String Or Binary Data Would Be Truncated...'

Oct 18, 2006

When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...

View 7 Replies View Related

SQL Server 2012 :: SELECT A String

Dec 1, 2014

I have string as below:

InvoiceTemplateId= SOURCE.InvoiceTemplateId
,Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

I need a string Like below:

Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

So I need every thing except the First value before first comma .

View 3 Replies View Related

SQL Server 2012 :: Find Numbers From A String

Apr 8, 2014

I have a string and i want to get only the numbers from right.

For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.

I got a function where it gives me all the numbers in a string but I don't need that

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT

[Code] ....

If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select

SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.

View 8 Replies View Related

SQL Server 2012 :: Convert Parts Of String To Int

May 2, 2014

I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".

I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.

CREATE TABLE #tmp(duration VARCHAR(20))

INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec

And this is what I have so far

SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0

[Code] ...

I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.

View 3 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 Server 2012 :: Group By Part Of String

Jan 8, 2015

I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.

"GROUP BY LIKE '%ThePartToGroupBy%'"

View 9 Replies View Related

SQL Server 2012 :: Parse A String Every 6 Letters

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)

INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

After parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.

View 2 Replies View Related

SQL Server 2012 :: Getting String Out Of Data From A Column?

May 7, 2015

I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below

Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information

sample data from system_id_information column

########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>

[Code] .....

I am looking output of two columns, which are bolded

product_id snumber
654081-B21 MXQ43905SW

for serial number this is common

before string :HostSystemIdentificationInfo"><identifierValue>

and after string </identifierValue><identifierType><label>Service tag

and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies

View 9 Replies View Related

SQL Server 2012 :: SET 1 Symbol Instead Whole String In Varchar Value

Jun 12, 2015

I have such Function:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'TwoDigitsNumber' AND type = 'FN')
DROP FUNCTION MinimumOFThree;
GO
CREATE FUNCTION TwoDigitsNumber(@a int)
RETURNS nvarchar(20)

[Code] ....

The only first letter 'f', 's', 'e' is inserted in value instead 'first', 'second', 'equal'.

Why ? How can i insert whole string

View 9 Replies View Related

SQL Server 2012 :: String To Date Without Time

Sep 15, 2015

I have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.

View 5 Replies View Related

SQL Server 2012 :: Dynamic String Replacement

Sep 16, 2015

I have a table with the following data;

CREATE TABLE #Tab (Data Varchar(100))

INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=10,Banana=11' UNION ALL
Select 'Apple=10,Orange=1033,Banana=0' UNION ALL
Select 'Apple = 120,Orange = 1,Banana = 112'
Select * from #Tab

How do I replace every value before the '=' but leave the comma.

Here is what the final output should look like

CREATE TABLE #TabFinal (Data Varchar(100))

INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'

Select * from #TabFinal

View 9 Replies View Related

SQL Server 2012 :: All Permutations Of A Single String

Sep 20, 2015

How to retrieve all possible sets of values from a table, with each set having a unique identifier.

Here's sample data, where any number of rows can be in the "animal" set:

select animal.name
from (
select 'Cat' as name
union all select 'Dog'
union all select 'Bird'
) animalHere's what I'm trying to get:

[Code] ....

It'd be an easy task if I knew how many rows were in the set, but without knowing how many (and being able to do x number of cross joins and CUBE/ROLLUP to produce the list of permutations) or writing a horrid complex of while loops, I'm at a loss.

A single column. All permutations of the values in a single column. Perhaps I should stay off here nearer the end of the day .

View 2 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 :: 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 :: Removing Character (Letters Only) From A String

Dec 19, 2014

I need to build a query, or use a function to removing alfabetic Character from a string.

My string is
WI14000323-0003

The value i want to keep is : 14000323-0003.

I try using this SELECT:

SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable

but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.

View 2 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 :: String Variables Comparison Function

Aug 10, 2015

What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.

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







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