T-SQL (SS2K8) :: How To Shorten A String

Mar 18, 2014

How do you take a string and search for the first "-" and then drop everything from the start to, and including, the "-"?

View 5 Replies


ADVERTISEMENT

Shorten Query

Jan 11, 2008



hi
below is my procedure..any other good way to write this procedures .. shorter and sweet

thanx

CREATE PROCEDURE sprc_HR_WriteAllUserAccessLog
@ClientID int,
@FieldID int,
@InsertedNewValue varchar(50),
@DeletedOldValue varchar(50),
@ChangedBy int,
@ModuleID int
AS
BEGIN
DECLARE @ModuleName varchar(10)
IF (@ModuleID = 1)
BEGIN
SELECT @ModuleName = 'HR'
END
IF (@ModuleID = 2)
BEGIN
SELECT @ModuleName = 'Payroll'
END
IF (@ModuleID = 4)
BEGIN
SELECT @ModuleName = 'Rotas'
END
IF (@ModuleID = 12)
BEGIN
SELECT @ModuleName = 'Tronc'
END
IF (@ModuleID = 10)
BEGIN
SELECT @ModuleName = 'Cash Management'
END
IF (@ModuleID = 23)
BEGIN
SELECT @ModuleName = 'Benefits & Expenses'
END
IF (@ModuleID = 21)
BEGIN
SELECT @ModuleName = 'Training'
END
IF (@ModuleID = 25)
BEGIN
SELECT @ModuleName = 'MI'
END
IF (@ModuleID = 26)
BEGIN
SELECT @ModuleName = 'Recruitment'
END
IF (@ModuleID = 30)
BEGIN
SELECT @ModuleName = 'Fit to Trade'
END
IF (@InsertedNewValue = '0')
BEGIN
SELECT @InsertedNewValue = 'None'
END
IF (@InsertedNewValue = '1')
BEGIN
SELECT @InsertedNewValue = 'Locations - Own Only'
END
IF (@InsertedNewValue = '2')
BEGIN
SELECT @InsertedNewValue = 'Locations - Bespoke'
END
IF (@InsertedNewValue = '3')
BEGIN
SELECT @InsertedNewValue = 'Locations - All'
END
IF (@DeletedOldValue = '0')
BEGIN
SELECT @DeletedOldValue = 'None'
END
IF (@DeletedOldValue = '1')
BEGIN
SELECT @DeletedOldValue = 'Locations - Own Only'
END
IF (@DeletedOldValue = '2')
BEGIN
SELECT @DeletedOldValue = 'Locations - Bespoke'
END
IF (@DeletedOldValue = '3')
BEGIN
SELECT @DeletedOldValue = 'Locations - All'
END

INSERT INTO t_ClientHistory (ClientID, FieldID, OldValue, NewValue, ChangeDate, ChangedBy, ModuleName)
VALUES
(@ClientID, @FieldID, @DeletedOldValue,@InsertedNewValue,GetDate(), @ChangedBy, @ModuleName)
IF @@ERROR <> 0
RETURN(0)
ELSE
RETURN(1)
END

View 8 Replies View Related

T-SQL (SS2K8) :: Returning MAX (Value) In String

Jul 29, 2014

I need to create a TSQL to return MAX(Value) removing the first part and last part.

Example

I have these Varchar Reference code:

1.00001-Q1
2.00100-Q2
3.00005-Q4

I need to cut the string to find the max number excluding (Part1):

1.
2.
3.

And also excluding (Part3):

-Q1
-Q2
-Q4

In this case converting varchar to INT, the correct value that i want is: 00101

The middle part excluding (Part1 and Part3)

Then my final reference could be:

1.00101-Q1
or
2.00101-Q2
or
3.00101-Q4

View 3 Replies View Related

T-SQL (SS2K8) :: Parsing A String

May 18, 2015

I have a table Alert_Types with these fields :

alert_id,
alert_source,
body,

Now whenever a new alert is registered record goes in table like

alert_id = 1,
alert_source= document_detail_view,
body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty

it was simple to parse simple variables using replace functions. eg. REPLACE(@str, '@customer_name', @customer_name). It worked like mail merge.the converted string was then sent forward using a webservice.now my requirement is to add conditional values in body field e.g:

body = Document ID: @document_id
Customer Name: @customer_name
Item name: @item_name
Quantity: @qty
IF isnull(@rate, 0) > 0 Rate: @rate
IF isnull(@rate, 0) > 0 Amount: @amount

how can i parse strings like this. I'm open to change format of values for body field.

View 9 Replies View Related

T-SQL (SS2K8) :: Returning Only Portion Of String?

Jun 19, 2014

i need to cut my string on 3 portion, for exemple my string is :

a) 1.9999-Q1

b) 01.9999-Q11

I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:

a) 1
9999
Q1

b) 01
9999
Q11

View 9 Replies View Related

T-SQL (SS2K8) :: Determine Which Pattern Comes First In A String

Aug 20, 2014

DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:

1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result.
2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.

Unless I'm missing something. Short example is below.

WITH testctes (string, pattern) AS (
SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL
SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL
SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL
SELECT 'more_stuff.yoursminehershis_falafel', 'yours first'
)
SELECT string, pattern, ca.item, ca.itemnumber
FROM testctes
CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca

View 9 Replies View Related

T-SQL (SS2K8) :: Convert String To Date?

Sep 5, 2014

I'm importing dates into a table with Bulk insert

SET DATEFORMAT DMY

it works with dates e.g. "14/01/2009"

However sometimes I get dates in the format

"Fri 14/01/2009"

What is the best way to convert these.

I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields

LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))

View 9 Replies View Related

T-SQL (SS2K8) :: Increment String By Appending 01

Oct 27, 2014

I am having a

Source Table: #test1(studID, FIrstNAme,LasteName)

Target Table: #Test2(StudID, UserName)

Now I want to create usernames from #test1 by considering first character of first name and last name and if same combination found then append with 01.

Example if #test1 contains data as below:

1,Abhas, Pawar
2, Arun, Pawar
3, Ashis, Panday

Then i want to create username like:

apawar
apawar01
apanday02

but if same username exists in #test2 then i want to inser records as below:

first apawar will check in #test2, if not exists insert as it is:

if apawar01 exists in #test2 then, cretae apawar02 instead of apawar01

for next create apawar03 and insert and so on...

In brief I want to check created username eith #table2 and if same exists then first check if lower value available if not then create with lower value and insert.

View 5 Replies View Related

T-SQL (SS2K8) :: Add String WHERE Filter That Will Match Everything

Nov 19, 2014

I need to add a filter clause like
WHERE username = '%%%'

I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.

View 6 Replies View Related

T-SQL (SS2K8) :: Error In String By CONCAT

Jan 22, 2015

i have 2 table:

t1:
t1_idint
namenvarchar(10)

t2:
t2_idint
t1_idint
Sematnvarchar(50)

then i write following SQL Command

SELECT dbo.T1.t1_id, dbo.CONCAT(dbo.T2.product) AS product
FROM dbo.T1 INNER JOIN
dbo.T2 ON dbo.T1.t1_id = dbo.T2.t1_id
GROUP BY dbo.T1.t1_id

but following error is be shown. why? Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CONCAT", or the name is ambiguous.

View 3 Replies View Related

T-SQL (SS2K8) :: Concatenating String Without Cursor

Mar 27, 2015

Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?

I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.

View 8 Replies View Related

T-SQL (SS2K8) :: Generate Unique String ID

Jun 30, 2015

I need to generate unique string Id from the range AAAAA to PZZZZ. This id will be unique for each Users for e.g.

User ---- TransactionNo ---- UniqueId
--------------------------------------------
12001 --- 101 --- AAAAA
12001 ---- 102 --- AAAAB
12001 --- 103 --- AAAAC
12001 ---- 104 --- AAAAD

13001 --- 105 --- AAAAA
13001 ---- 106 --- AAAAB
13001 --- 107 --- AAAAC
13001 ---- 108 --- AAAAD

more the records of each user, unique should be the ID as displayed above.. Optimized way to generate the same...

View 3 Replies View Related

T-SQL (SS2K8) :: First Occurrence Of The String From Text

Sep 4, 2015

I have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.

TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'

From the above text1, I want to get “AXNARENDR”.

Based on logic defined below:

First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”

In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string

i.e AXNARENDR (TEXT1 I defined “A” with 4th occur

TEXT 2:

'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABN
AGENDRACSURENDRADJITHENAYENDGHRABVEERNDARAXDRMNDRABNAGENDRACSURENDRADJITHENAYRVINDR'

From the above text2, I want to get “AYENDGHR”.

TEXT 3:

'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHEN
ABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHAZENIVKHRABVEERNDARAXDRMNDRAYRVINDR AZNHKLMN'

From the above text3, I want to get “AZENIVKHR”.

View 9 Replies View Related

T-SQL (SS2K8) :: Find Occurrence And Remove String?

Mar 19, 2014

My sample data is as below.

plan type A change from plan type B from Plan type C

Insurance plan M changed from Insurance plan b From plan d from Plan N

Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.

plan type A change from plan type B

Insurance plan M changed from Insurance plan b

View 3 Replies View Related

T-SQL (SS2K8) :: Start Position Of Number Value In A String

May 2, 2014

In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.

This field can contain values like:

xxIN187 13-14 W Elem
HS321 13-14 D Elem
IN636 13-14 C Elem
030 13-14 clark middle.

What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.What I know that I want is substring(CalendarId,?,3).The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.

View 6 Replies View Related

T-SQL (SS2K8) :: Error Converting Datetime To String

May 29, 2014

I am trying to create a stored procedure that Pulls in Chargeable and Non Chargeable hours for our employees however When I run the Stored Procedure I get this error "Conversion failed when converting date and/or time from character string." I am having a hard time figuring out were this is happening in the Stored Procedure. Also I would like to be able to Add a parameter that would be the StartDate and EndDate for which the stored procedure would pull time for.

ALTER PROCEDURE [dbo].[Chargeability]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ......

View 9 Replies View Related

T-SQL (SS2K8) :: How To Cut Certain Values In A String To Separate Columns

Jun 5, 2014

I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.

The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)

EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"

The result would Be
column header US
with value Project/Prescription sale

next column header NL
with value Project/specificatie etc.

Here are table examples:

IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]

CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Missing Century In Date String

Jul 31, 2014

Any script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:

7/26/29 = converts to 2029.

I'm looking for a SQL statement that will now to put a 19 or 20 in the century.

View 9 Replies View Related

T-SQL (SS2K8) :: Split String To Show Only Customer

Aug 7, 2014

The data we are getting from our shipping company has the customer name and customer number attached.

so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'

So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)

I need to match this up with our customer table... on Customer Name.

View 9 Replies View Related

T-SQL (SS2K8) :: Replace String After Specific Index

Mar 23, 2015

I have data like below

Potter, James J
Williams, Ted R
Allen, Gary G

I want to remove Middle Name from the output

Potter, James
Williams, Ted
Allen, Gary

My Query:

SELECT
CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN
REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '')
ELSE Supervisor END AS NewSupervisor from data d

However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"

Do we have any way to say sql to replace after certain index?

View 3 Replies View Related

T-SQL (SS2K8) :: Conversion From Binary Version String

Apr 1, 2015

Trying to get this query to work, converting a binary version string to human readable output but somehow it doesn't work?

/* Version number binary from daily registy */
DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)

[code]....

View 6 Replies View Related

T-SQL (SS2K8) :: String Manipulation - Data Between Quotes

Apr 9, 2015

I have data in a trace file, and I need to extract some info such as phone number.The problem is the phone number could be varying lengths, and various positions in the row.

For example:

@City='New York', @Phone='2035551212' (10 characters, no dashes)
or
@City='San Francisco', @Phone='918-555-1212' (12 characters, with dashes)
or
@City+'Berlin', @Phone='55-123456-7890' (14 characters, with dashes)

I can use CHARINDEX to search & find @Phone=' so I know where the phone number starts, but stuck on a programatic way to find the data between the quotes since it can vary.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Add Multiple Spaces To Dynamic String

Jun 15, 2015

I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:

SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)

That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.

View 2 Replies View Related

T-SQL (SS2K8) :: Possible To Split String To NICK And SERVERS

Jun 17, 2015

Can it be possible to split the below string to NICK and SERVERS. Query which provides two columns, first column data should be NICK and other SERVERS.

_BF-TYPE:NICK_ _BF-SUBSYSTEM:SERVERS_

View 9 Replies View Related

T-SQL (SS2K8) :: Find Records In A String And Display

Jul 6, 2015

I am having a column which is ntext and contains below type of data.

{ "running":"true", "all":{ }, "GPAs" : [ {"type":"item", "alias":"i_11111"} ,"GPA":"1.75" }, {"element": {"type":"item", "alias":"i_11111"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_33333"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_44444"} ,"GPA":"1.43" }, {"element": {"type":"item", "alias":"i_22222"} ,"GPA":"1.42" }, {"element": {"type":"item", "alias":"i_55555"} ,"GPA":"15" }, {"element": {"type":"item", "alias":"i_66666"} ,"GPA":"10" } ]}

above is a single row. There are many such rows are present in the table. Now I want to find all alias and GPA and display as below.

idGPA

111111.75
111111.43
333331.43
444441.43

In oracle it can be done by using REGEXP_SUBSTR. But how can we do this in SQL?

View 1 Replies View Related

T-SQL (SS2K8) :: Query Result To String And Present In New Row?

Oct 21, 2015

I have made following code:

I want to make a string from "TarLang.AppendText(String.Format("{1}" & vbCrLf, r("Source Language"), r("Target Language")))" and the result I get when I use that.

add the result into a new row in the datagridview

Dim CompanyID As String
CompanyID = OrderNR.SelectedItem.ToString()
SQL.ExecQuery(String.Format("SELECT snSrc.kod as 'Source Language', snTrg.kod as 'Target Language' FROM [teknotrans_dev].dbo.OpusOrderrow as ord INNER JOIN [teknotrans_dev].dbo.OrderVolvoLanguageName as snSrc

[code].....

View 2 Replies View Related

T-SQL (SS2K8) :: Split Pipe Delimited String Into Two Columns

Mar 6, 2014

I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.

Is there a way I can extract the values from the string into two separate columns?

XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c

View 5 Replies View Related

T-SQL (SS2K8) :: Convert Binary String To Table Value Construct?

Mar 21, 2014

Code snippet for changing '00001001001' into a table value construct containing (64,8,1)?

View 6 Replies View Related

T-SQL (SS2K8) :: String Occurrence Count With Cross Apply

Jun 17, 2014

See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.

DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))

INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL

[Code] .....

View 9 Replies View Related

T-SQL (SS2K8) :: How To Use Substring And Charindex To Extract Desired String

Jul 9, 2014

I have a column that contains the follwoing string I need to compare.

ek/df/cv/
ek/df/cv/f

All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.

I can't do it by using the combination of Substring() and charindex() and Len()

View 9 Replies View Related

T-SQL (SS2K8) :: Trying To Use Substring (charindex) To Remove Start Of A String

Aug 26, 2014

Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.

An example string

declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2

I've tried

select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'

And it fails dismally...

View 8 Replies View Related

T-SQL (SS2K8) :: Replacing String Values With Contents Of Variable

Dec 19, 2014

So I have the following column named String in a table:

<key>Children</key><integer>2</integer>

This of course can vary for the different records. What's the best way to replace the 2 with the contents of my variable with TSQL?

declare @children int
set @children = 4

I want to do something like this:

SELECT <key>Children</key><integer>@children</integer>

View 4 Replies View Related

T-SQL (SS2K8) :: How To Ignore String Or Binary Data Would Be Truncated

Jan 16, 2015

I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take:

1. script the source table's schema to a create
2. the new script:

If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
[IP] [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
insert into EventlogHistory

It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?

View 9 Replies View Related







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