I am having trouble using the replace statement on IP Addresses. I want to replace the ending with ".0" - not matter what the IP Address starts with.
175.139.45.127
175.139.45.12
175.139.45.1
10.10.10.100
10.10.10.10
10.10.10.1
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....
I want to gather (and filter by) client ip address in an extended events session. So far, I have the sqlserver.login event added, but I can find no way to get client ip address added to the action or predicate.
I can filter by the pred_source field "session_nt_domain" or "nt_domain" but this is not always populated by all logins and also, this domain only lists machine name, such as FRED, as opposed to the full domain FRED.foo.bar.
Is it possible to gather login client ip address in an extended events session? Surely internal SQL Server processes have access to it because the login audit can populate client ip address.
i have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change.
dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk
Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;
dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;
dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;
dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
w:http://www.yahoo.com/faqs;
I have a very interesting problem in T-SQL coding for which I can't figure out the solution. Actually there is a Line_1_Address column in our data warehouse address table which is being populated from various sources. Some sources have already concatenated house number + street address fields in the Line_1_Address column whereas one source has separated columns for both data fields.
Now I'm trying to extract data from this data warehouse table and I need to split the house number from street address and load it into separate columns in my destination table. In case there is no data for house number then I should load it as NULL.
The issue is that data in this Line_1_Address column is very inconsistent so I don't know which functions to use. Here is some sample data for your consideration:
Line_1_Address
101 E Commerce ST
120 E Commerce ST
2 Po Box
301 W. Bel Air Ave
West Main Street, PO Box 1388
[Code] .....
Hi ;)
I have a select statement where i need to Replace some Chars
Maybe someone can help
SELECT * FROM reguser WHERE REPLACE(" & whereTable & ",'-','')
I want to Replace the "-" and the "/"
Thanks in advance
I have a SQL table which has field data like below
field a field b
123 KB 246 kb
How can I strip out just the numbers(there will always be a space between the number and KB?
Thanks
how should i use replace function in sql statement??
in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.
my stored procedure accepts two parameters.
Is there any way to solve my problem??
Jaimin
Hi,
there is a table "attribute_definitions"
Field Name Type Size Key--------------------------------------------------
attr_name char 25 PK
attr_value nvarchar 250 PK
comment nvarchar 100
the records in the table are
attr_name attr_value comment------------------------------------------------------------------
mail dasari.kanth@gmail.com nothing
mail xyz@gmail.com nothing
UID test123 nothing
--------------------------------------------------------------------
now i am trying to update this table with the below UPDATE command
update attribute_definitions set attr_value=replace(attr_value,'gmail','yahoo') where attr_name='mail'
but the above update statement giving the error like
"Violation of PRIMARY KEY constraint 'PK_attribute_definitions'. Cannot insert duplicate key in object 'attribute_definitions'."
please can any one help me [?]
Hi,
I've to translate this SQL-Statement from ORACLE to SQL-Server. But I'm missing the Minus-Statement on SQL-Server.
select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
minus
select tab_name from data_dic
)
minus
select tab_name, col_name from data_dic
what can I do to run it on SQL-Server.
Thanks in advance
Raimund
We have a multi-site AG and are demoting one of the remote sites out of the AG. In doing so, we've discovered that no logic exists to remove an IP address from the listener.
It seems that the ALTER AVAILABILITY GROUP MODIFY LISTENER logic lacks functionality other than ADD. [URL] ....
I'm afraid to just remove the IP address from the cluster object as that IP is also stored in the HADR systables.
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
When I import my tables from MySQL, it put "
" with the contents in
the field. Will someone show me a SQL statement that I can use to clean
all the "
" out of the table's contents?
Thanks!
I refresh QA environments with copies of our production database quite often. Many of the users also have read-only logins to production, but not all. I've noticed that in QA the users in the restored database are matched up with QA server logins that no longer have "DBO" as their default schema. We almost always use DBO, nothing else.
On the most recent restore, I didn't drop the target DB first, just restored with "replace." Does it matter whether I drop or replace in this instance?
The one user reporting issues could not open the database in management studio to view objects/tables etc. I noticed their default schema was their domain login, so fixed that and they now no longer have the issue.
I am having an issue when trying to do something that looks simple on the face of it.. I want to replace the value during update in the same table.
Here's my situation
tst_update
ID int
Col1 varchar(10
I want to ensure that everytime Col1 is updated to "A" I want to set it to "X". Otherwise leave it as is.
Can someone help.
Thanks,
Ashish
We require to convert a list of SPs in to SSIS packages. Most of the SPs do the below steps:
mainly our store procedure r to have compare the present date to past date , and comparing emp id between the files and also some joins. updating table r take place.
I have a text field with about 17,500 rows I need to edit from for example: '1.90X .90' to '1.90X 0.90'? The numbers are various but follow the basic format albiet they may vary to say '22.78X .40'
View 8 Replies View RelatedI would like to replace the value in the Select query of CO OWNER with a space ' ' or NULL if the ORDINAL value = 0.
The CO OWNER name is stored in the CARDNAME table, and the OWNER name is stored in the NAME table. I can not change the db structure.
JEAN is the ACCOUNT owner and BILL is CO OWNER of two cards.
Current Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 BILL JEAN
Desired Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 NULL JEAN
Current SQL Select statement:
SELECT DISTINCT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST AS CO_OWNER,
[code]....
using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.
select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns
I need one query...
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
[Code] ....
I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
[code]...
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.
I'm attempting to improve a vendor created query.
Here is the original -
SELECT m.lID
FROM Message m
inner join Message_Cc mCC on m.lID=mCC.lMessage and mCC.lOfficeRecipient = 200321
INNER JOIN UserRole d on mCC.szRecipient=d.szRoleName
inner Join Map_UserAtOfficeToRole a2 on a2.lUserRole = d.lid AND d.nRecordStatus = 1
[Code] ....
If I run this without the LEFT OUTER JOIN and the is null statement I get 648 rows. But If I include it I get 0 rows. I can't understand why I get 0 rows with the outer join.
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 RelatedOk I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl
SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
I am looking to create a simple IF Else statement in a SPROC but my syantax seems to be wrong.
IF (vchDept='IT')
BEGIN
THEN 1
END
ELSE
IF vchEmail like '%@abc.com' AND vchDept<>1
BEGIN
THEN 2
END
[code]....
SELECT
SUM(((CASE WHEN
o.date>= a.activity_date, other filter condition, other filter condition
THEN
(select coalesce(d.balance,d2.balance) from drawtable d where coalesce(d.date, d2.date) < a.activity_date order by d.date desc limit 1) - ( select coalesce(d.balance, d2.balance) from drawtable d where coalesce(d.date, d2.date) = interval 'current date'
else end ))
from
emailtable a
LEFT JOIN opportunity o
left join drawtable d
left join drawtable d2
etc
The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement?
I have successfully built a messaging system into my application, I now in the process of displaying the messages in the UI.
The following are how my tables are constructed.
CREATE TABLE [MailBox].[Message](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SenderId] [bigint] NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[SentDate] [datetime] NOT NULL,
CONSTRAINT [PK_MailBox.Message] PRIMARY KEY CLUSTERED
[Code] .....
Now I haven't set the foreign key on the MessageReceipient table yet. When someone sends me an email I insert a record into [MailBox].[Message] and output the insert id into MessageReceipient along with the ReceipientId this is working as expected, when I then click on my inbox I call the following stored procedure:
Select p.Username, count(mr.RecipientId) [TotalMessages],
CASE
WHEN mr.ReadDate is null then 1 -- New message
WHEN mr.ReadDate is not null then 0 -- Message has been read
END AS NewMessage
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
GROUP BY p.Username, mr.RecipientId, mr.ReadDate
This will give me the person who has emailed me, the total amount of messages and if the message is new or its been read, I do this by checking the ReadDate column as shown in the case statement (but this gives me duplicates, which is not what I want). Lets say user1 emails me 5 times so when I call this proc I will have the same user displayed to me 5 times, what I'm trying to achieve with the proc is it will show User1 as the following:
User1 5 Messages 1 or 0 New Messages
I can get it to display as follow when I remove the case statement
User1 5 Messages
but as soon as I add the case statement back in then I get 5 rows.
How can I change this proc in such a way that it will display the data as follows;
User1 5 Messages 1 or 0 New Messages
New Messages is dependent on ReadDate if its null then we have a new message, otherwise its been read.
I'm trying to create a case statement that if a field = a certain code, I'd like to take another field * 0.9. But, I'm getting a Null value for the answer..here is the statement:,case when parts.ndc = '50242-0138-01' then labels.BAGSDISP*0.9 end "Units Dispensed"..For this example labels.BAGSDISP is a value of 2. So, in theory it should be 2 * 0.9 and the result should be 1.8 but I'm getting a NULL
View 9 Replies View RelatedI have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.I am trying to write a case statement like so:
CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A'
THEN 'ADJ'
WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR'
THEN 'REV'
WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR'
THEN 'CREDIT'
ELSE NULL END AS 'Status'
For the most part the code is working, with the exception of the fields that just end in 'R'. An example of this is 471268R, 2525125901CRR, 11100325230CR
Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal). How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?
adding a if statement within my cursor.
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
[code]....
The cursor should only grant access to a particular database. If the user exists within the database it should not execute the script.
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead